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 |
|
Grouchie
Starting Member
7 Posts |
 Posted - 2004-03-16 : 14:16:29
|
| I recently aquired all of the SQL servers in my office. I am trying to go through each database on each sql server and get a listing of which users have what rights.... but I am having a bit of trouble...I am using sp_helpUsers, and sp_helplogin... I have a user who has rights to execute all stored procedures, but sp_helpUsers, and sp_helplogin are not showing me the stored procedures this user can execute... Is there a way to do this? I really want to see exactly what users(mixed security mode) have what rights to each database(select, insert, delete, update, execute on stored procedures, etc)... Any help would be greatly appreciated... |
|
|
MuadDBA
628 Posts |
Posted - 2004-03-16 : 14:25:34
|
| Easiest way to start is to look at ROLES, both server and db level. The problem is you can make a user part of one of these roles, and it won't show up as being explicitly granted any permissions. They inheirit the permissions from the roles. Thus someone grantd db_owner role will be able to execute all stored procedures, but won't show up as having exec prviledges to any of them if you query by user. |
 |
|
|
Grouchie
Starting Member
7 Posts |
Posted - 2004-03-16 : 15:35:27
|
| My problem is this guy is not a member of any role, he just has exec rights to all the stored procedures on a database, and i want to be able to document that... I also want to know about other users that might also have stored procedure execute rights and make sure I can document that....any help greatly appreciated. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-16 : 17:27:53
|
| I think you want sp_helprotect. This will give you all explicitly granted permissions. |
|
|
|
|
|
|
|
|
Questions about finding out who has what rights...