Auditing your SQL Server database and server permissions

By: Greg Robidoux   |   Read Comments (1)   |   Related Tips: More > Auditing and Compliance

Problem
One of the things that DBAs need to be aware of is the permissions that are granted at the server level and at the database level.  Using Enterprise Manager or Management Studio it is pretty easy to look at one object at a time, but what if you want to look at permissions you have granted across the board.  Pointing and clicking is not so bad if you have a couple of objects to look at, but most instances of SQL Server have several layers of permissions that are granted.  So what is the best way to get an overall look at your permissions that have been set?

Solution
The simplest approach is to use the stored procedures that Microsoft has included with the database engine.  A lot of these same procedures are called when use the GUI, but instead of showing you data one object at a time you can take a look across your server or across your database. 

Here is a list of some of these useful commands, what they do and sample output from each command.

sp_helprotect	This command will show you the permissions that have been granted or denied for all objects in a database. You can also specify the object name to see the permissions for that just that object.
sp_helprole	This command will show you a list of all the database roles  You can also specify the role name to see information about just that role.
sp_helprolemember	This command will show you a list of all roles that have users in them as well as the user name.  You can also specify the role name to see the users for just that role.
sp_helpsrvrole	This command will show you a list of all the server roles.  You can also specify the server role if you only want to see info about just that one server role.
sp_helpsrvrolemember	This command shows you logins that have access to all server roles or you can specify just one server role to examine.
sp_helpdbfixedrole	This command shows a list of fixed database roles.  You can also specify just one role.
sp_helplogins	This command returns attributes about all of your logins or you can specify just one login.
sp_helpntgroup	This command shows you windows groups that have access to the current database.
sp_helpuser	This command shows you information about users that have access to the current database.

Next Steps

• Add these commands to the list of tools that you use to manage your SQL Server. These commands work for both SQL 2000 and SQL 2005
• Use these commands to take a periodic audit of your server and database permissions.

Last Update: 9/27/2006

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Tuesday, October 09, 2012 - 2:56:28 PM - Matthew	Read The Tip
Thank you Greg. This was a very helpful article; just what I needed. :)

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. Secure column & whole database on all versions and editions of SQL Server with NetLib’s TDE SQL Server Data Tools - Got questions? Get the answers here!