Auditing your SQL Server database and server permissions

By:   |   Comments (2)   |   Related: > Auditing and Compliance


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?


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.  You can just run the stored procedure without parameters to get the output.

SQL Server 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. For more information: sp_helprotect.

role name

SQL Server 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. For more information: sp_helprole


SQL Server 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. For more information: sp_helprolemember.

member name

SQL Server 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. For more information: sp_helpsrvrole.

server role

SQL Server sp_helpsrvrolemember

This command shows you logins that have access to all server roles or you can specify just one server role to examine. For more information: sp_helpsrvrolemember.

member name

SQL Server sp_helpdbfixedrole

This command shows a list of fixed database roles.  You can also specify just one role. For more information: sp_helpdbfixedrole.

fixed role

SQL Server sp_helplogins

This command returns attributes about all of your logins or you can specify just one login. For more information: sp_helplogins.

login name
login name

SQL Server sp_helpntgroup

This command shows you windows groups that have access to the current database. For more information: sp_helpntgroup.

group name

SQL Server sp_helpuser

This command shows you information about users that have access to the current database. For more information: sp_helpuser

user name
Next Steps
  • Add these commands to the list of tools that you use to manage your SQL Server.
  • Use these commands to take a periodic audit of your server and database permissions.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, July 11, 2018 - 3:01:10 PM - Mike Schelstrate Back To Top (76612)

 Very helpful information about previously unknown security system stored procedures!

Tuesday, October 9, 2012 - 2:56:28 PM - Matthew Back To Top (19839)

Thank you Greg. This was a very helpful article; just what I needed. :)

get free sql tips
agree to terms