We are working on an application but with other users who have been using the SA password. The application is running on MSSQL 6.5 and sp4.
We have been generally assigning logins/userids to new users although some people are using the SA id. The SA id password has been changed so that will stop users (really developers) from using it.
Most users are assigned to a group which has select, insert, update, delete
permission. Some users will be moved to a group which as only select privilege
I would like to query the system tables and see exactly what permissions
are assigned to each user.
I can look at Enterprise manager and cut and paste this information I think.This is cumbersome
1) Does anyone have a query I can run to get the userid/login and the permissions for each userid?
2) How can I make a user a dbo and give him/her the same permissions as a dbo on tablesalready creaetd?
Hello All,Does SQL Sever 2000 keep track of all the logins/userids that weredeleted over the last year? For example, If I deleted a login "Joe"from a SQL 2000 server, (and of course the corresponding userid "Joe"got deleted from the database) would that be recorded somewhere in thesystem or the production database? i.e. the information such as thelogin "joe" deleted on such and such date from such and such database?Long Live SOX :)Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
In my local instance of SQL Server at work (which I use for testing), somehow all of the logins except 2 were dropped. The administrators group no longer exists as a login (nor do any other windows users or groups)-the only 2 remaining are sa (which is disabled) and a SQL login I had created earlier which has no permissions except read permission on master. I can login as this SQL user, but I do not have permissions to create logins or enable the sa account. Do I have any options other than uninstalling and reinstalling my local instance?
I am at a company with 18 employees and I have 11-12 databases in SQL server. I can't seem to give logins and permissions to groups. Is there a simpler way, or do I have to add every single employee to each database and give permissions?
I need to move several databases to a new server while retaining the same logins/permissions. Books Online indicates that DTS can move the SQL Server logins, but it sets the passwords to NULL in the process. Is there any way to move the logins and keep all passwords/permissions intact?
I know that there is Microsoft KB to migrate SQL Logins but it doesnt take care of Login Server level permissions or User level permissions.Idera used to have a Free tool SQLPermisions.exe but it works only on Windows XP/Vista not on Windows 7. Any third party tool (free or paid) which can migrate SQL Logins and User permissions ?
Hi,I work as an IT Administrator and part of my job role is to useEnterprise Manager and Query Analyzer to backup the logins andpermissions for each database on our SQL Servers. This information isused as a backup in the event of a server failing so we then havesomething to fall back on if we need to add the same logins/permissionsagain.This takes ages to do, as I have to manually enter all the informationinto a spreadsheet.Steps:- In Enterprise Manager I connect to a server then 'Security' tab, then'Logins'. I then enter all items into a spreadsheet.- In Query Analyzer I connect to a server then do 'sp_helpuser' to listall the permissions for each database. I then enter all informationinto a spreadsheet.I was wondering if there is such a program available on the market thatwould do this for me? I know that MS Baseline Security can show flatfile share permissions to a certain degree and was wondering if therewas a similar program out there to help me do my job easier, or ifthere is a simple way of doing this in the future...Any feedback would be greatly appreciated.Regards,Jenny
Hello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.
Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
Our company has 2 Database Roles (DBE and DBA). The DBE creates database schema, performs SQL Server Administration, and manages server security. The DBA writes data access, ETL, and manages database security. In 2005, we're struggling with how to allow the DBA to see all of the logins on the server in order to add them as users of their database. What permissions does the DBA need to select from any of the logins on the server to add them to their database?
Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.
A question on the permissions hierarchy: Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?
Has anybody been successful migrating userids from one server to another server using SQL7.0? If so, is anyone willing to please provide any sample scripts.
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Folks, I'm brand new to MSSQL, I come from a DB2 background.
I've installed SQL Server Express Edition on my WinXP (SP2) system. I'm able to connect to it from a command prompt using "sqlcmd -S CS288290-ASQLEXPRESS -e".
Now I want to connect using a specific UserId and Password. So I changed my "LoginMode" registry variable to "0x00000002" and restarted the service. But now when I issue "sqlcmd -S CS288290-ASQLEXPRESS -U g2user -P g2pwd", I get error:
Msg 18456, Level 14, State 1, Server CS288290-ASQLEXPRESS, Line 1 Login failed for user 'g2user'.
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem? Please, help Thanks
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service. When we install the app, we run a process that builds 2 dbs to include: Tables, indexes, stored procedures, views and user accounts. SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names. We have a client that wants to remove our standard logins so that they can run on only a Windows login. I know I should be able to tie the db users to a Windows login. And I can do the same for the service.
But I am at a loss as to how to get this done. How do you associate db users with a Windows login? When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
I need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later. I clearly need to maintain all the security options for the new logins. Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that? Thank you.
I have two servers (server1, server2). I was trying to access the data from server1 to server2. I linked the servers and set up the option for data access. when I run the following command from server1: sp_remoteoption 'server2', 'sa', 'sa', TRUSTED, TRUE
It gives me the following error. How to handle this.
Server: Msg 15185, Level 16, State 1, Line 0 There is no remote user 'sa' mapped to local user 'sa' from the remote server 'server2'.
I have a server that was recently upgraded from SQL 6.5 to 7.0 that contains almost 700 logins using standard security. Is there a limit to the number of logins that SQL will host? The logins/users have all been added using a GUI within an application and does not support NT authentication. Does this cause any known problems in SQL 7? When I right click on the database and go to properties / permissions MMC gets hung up. Also one of the systems analysts is convinced that SQL is dropping permissions (I disagree to this). Any one have any thoughts or experience with large numbers of logins / users?
with sql 2005 1)I create a windows login in my home pc (win xp home sp2) and i can connect server with ssms this new user
but in my office (win xp pro sp2) create a windows login (it is a windows user also) and when try to connect with ssms take error 18456 (user name and pass is true!!!)
2)in my office pc i create a sql login which has no role. but this user can create users and dbs. but in my home pc, user has no role cant do thats...:eek:
Hi everyone,I am new to this forum.I hope some body will help me in this,I am trying this for the past 6hrs. on my server there are some hundreds of sql logins are there,I need to identify the logins which doesn't associated with either any databases or dbroles,server roles.Later i need to delete those logins.Can any body help me in this.
I want to generate script for users and their associated permissions on the xyz database and at the same time I want to generate script for associated logins and their roles over the xyz database.
We created web site which has NT Authentication....
Using service account SERVICE ACCOUNT WEBGROUP This has WEBGROUP_USERS
WEBGROUP_USERS Tracey John
When myself logs in... Connect To SQL i see
in sysprocesses WEBGROUP
Is there a way to pass TRACEY to the process rather than having SQL Login set up..
Reason is if someone adds themselves to this group in AD they could have access to our applications im trying to figure out how to not make AD drive the access to SQL.
