SQL Server Performance Forum – Threads Archive

Auditing database permissions

Is there a quick way I can get all the permissions that the various SQL and NT groups have access to? I could just go through each group and user and check what they have but there is surely a nice T-SQL command I can use? For all the groups and users I need to know which databases and whether they have public, dbo access etc etc

---

use sp_helprotect to get the information.
Refer to BOL for more information. _________
Satya SKJ

---

Tried that but…..the results I get do not list actual permissions I am doing this Selecting the database and executing exec sp_helprotect @permissionarea = ‘o s’ The results I get list the NT groups but NOT the permission they have. If I do it manually for one of my groups I see that they have access to a database and have the permission. I am wanting to see something like Login, database, database permission (i.e. dbo, db_ddladmin etc) I don’t see any reference to the terms used in the logins section – perhaps this query is giving me more detail than I am wanting? Found what I wanted!! I needed to know role membership – NOT permissions so I am using the sp_helprolemember command

---

Thats valid, nice catch keep it up. _________
Satya SKJ

---

]]>