Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2007-09-07 : 09:22:27
|
| Hi All,I need to find out the permissions a login has on all the databases on production server. I need to find that for all the sql logins (employees) who left the company. Just wanted to make sure their access is removed from all the databases.Can anyone tell me how to do it? Is there any system stored procedure to find that out?Thanks in advance-P |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-07 : 09:58:49
|
| To find out which databases they are mapped tojust look at sysusers in the database.To see what the user has permsissions on see syspermissions in the database.Note - users can gain permssions via a role too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-08 : 00:10:07
|
| Tried sp_helplogins? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-08 : 14:54:36
|
| an ounce of prevention is better than a pound of cureuse windows authentication for direct database access, this way your AD admin manages this type of cleanups...1. create AD group for these2. add AD accounts3. add AD group to sql server4. provide rightsso when your admin removes the account in the group, the person's db permissions gets removed as well--------------------keeping it simple... |
|
|
|
|
|
|
User permissions