SQL Server Performance Forum – Threads Archive

Database Role

There are 100’s of SQL server role defined in database by users, How to audit database role’s and script what permuission or role does each database role have or what objects does this database role access and with what permissions??

---

SQL BOL Says: You can get a list of the fixed database roles from sp_helpdbfixedrole, and get the specific permissions for each role from sp_dbfixedrolepermission. http://msdn2.microsoft.com/en-us/library/aa214303(sql.80).aspx
Need more informations? Deepak Kumar
MVP, MCDBA – SQL Server
[email protected] Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

---

You can get the object permissions info from sp_helprotect but it will give object level access…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

sp_helprotect has some limitation in 2005. BOL :
sp_helprotect does not return information about securables that are introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead. Madhu

---

DMVs has got full scale of advantages in this case, in the case of particular role in your database you can take help of Profilerhttp://msdn2.microsoft.com/en-us/library/ms191148.aspx as here. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>