Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Simplify SQL Server Database Development     ====>    Webcast Registration

Auditing SQL Server Permissions and Roles for the Server

By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | More > Auditing and Compliance


I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?


After you've audited logins, as detailed in a previous tip, you'll want to look at auditing server permissions and server roles. Depending on your version of SQL Server, there are different approaches you'll have to take. For SQL Server 2000 and earlier, permissions at the server level are granted only through pre-existing server roles, otherwise known as fixed server roles. As of SQL Server 2005, permissions can be assigned outside of the roles, meaning we'll have to do a bit more querying to capture all of the information needed. Let's look at each version separately.

SQL Server 2000

For SQL Server 2000, we're only concerned with membership in the fixed server roles. The best way to get this information is through the use of the system stored procedure sp_helpsrvrolemember. While this stored procedure can take parameters, if we want members of every server role, we can simply issue the stored procedure without parameters like so:

EXEC sp_helpsrvrolemember;  

The problem with just using the stored procedure, though, is we have no control over sorting or anything else. The best way to handle this, then, is to dump the output of the stored procedure to a temporary table and then use it to query against, along with syslogins:

CREATE TABLE #ServerRoles (
ServerRole VARCHAR(20),
MemberName sysname,

INSERT INTO #ServerRoles
EXEC sp_helpsrvrolemember;

SELECT SL.nameSR.ServerRole
FROM syslogins SL
JOIN #ServerRoles SR
ON SL.sid SR.sid
ORDER BY SL.nameSR.ServerRole;
DROP TABLE #ServerRoles;

Since there isn't the capacity to grant specific permissions at the server level for SQL Server 2000, this is as far as we need to go.

SQL Server 2005/2008

Because SQL Server 2005 introduced a new security model where permissions can be assigned against Securables, we must delve a little deeper in the later versions to get all of the information we need for an audit. There are several catalog views we're interested in:

  • sys.server_principals
  • sys.server_permissions
  • sys.server_role_members

To get the server role members we'll combine sys.server_principals and sys.server_role_members like so:

SELECT SP1.[name] AS 'Login'SP2.[name] AS 'ServerRole'
FROM sys.server_principals SP1
JOIN sys.server_role_members SRM
ON SP1.principal_id SRM.member_principal_id
JOIN sys.server_principals SP2
ON SRM.role_principal_id SP2.principal_id
ORDER BY SP1.[name]SP2.[name];

To get the permissions, we'll key in on sys.server_principals and sys.server_permissions:

SELECT SP.[name] AS 'Login' SPerm.state_desc ' ' SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
JOIN sys.server_permissions SPerm
ON SP.principal_id SPerm.grantee_principal_id
ORDER BY [Login][ServerPermission];

Do note, that if you're not interested in the CONNECT SQL permission, you can filter that out using an appropriate WHERE clause - WHERE NOT (SPerm.type = 'COSQ' AND SPerm.state = 'G') - which will eliminate that from the result set. Of course, we could combine the two to generate a query for a single report. Since we know there will be no duplications between the two SELECT statements, we can use UNION ALL and a slight modification to the first query (to indicate it's a role) to get the aggregate permissions for each login.

SELECT SP1.[name] AS 'Login''Role: ' SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission' 
FROM sys.server_principals SP1
JOIN sys.server_role_members SRM
ON SP1.principal_id SRM.member_principal_id
JOIN sys.server_principals SP2
ON SRM.role_principal_id SP2.principal_id
SELECT SP.[name] AS 'Login' SPerm.state_desc ' ' SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission'  FROM sys.server_principals SP 
JOIN sys.server_permissions SPerm 
ON SP.principal_id SPerm.grantee_principal_id 
ORDER BY [Login][ServerPermission]

And we're successfully auditing all the permissions at a server level with respect to SQL Server 2005 or 2008. Do note the COLLATE DATABASE_DEFAULT that is placed in the SELECT query for each second column. This is to prevent a collation conflict in the ORDER BY clause.

Next Steps
  • Take the time to automate this collection process.  Create some audit tables and setup Jobs to periodically collect the data and alert you to any changes.
  • Review the tip related to login auditing
  • Refer to these other tips related to security

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, December 17, 2014 - 12:11:31 AM - Vijay Back To Top

I am asked to audit the SQL Server Security logins on below two scenarios


1. If a new login created with sysadmin or db_owner permissions, this has to be tracked under a log table with information like targetloginname, type of permission (i.e. sysadmin or db_owner), createdby (who created this login), hostname (from which hostname), createdon (on which day it was created)


2. For an existing login, if sysadmin or db_owner permissions provided. This scenario also to be captured similar to first scenario.


How can we achieve this? Any help is highly appreciated.


Thanking You

Vijay Kumar M

Wednesday, June 19, 2013 - 3:39:17 PM - David Potter Back To Top

I do not know if this is normal or not.. but for some reason when you look at the sys.server_principals and then for that user go and look at sys.server_principals only their custom roles show up, no system roles. They still show up in the GUI. What is going on?


Not sure why my previous post had a different font.

Wednesday, October 20, 2010 - 3:34:39 PM - Yordan Georgiev Back To Top
How could I say it ?

Thank you , Thank you , THANK YOU !!!

Wednesday, October 06, 2010 - 10:47:08 AM - Udhuman Back To Top
Dear Sir,


I need Tracing and Auditing the Sql Server database so please give the step by step Guide



Thanking You


Learn more about SQL Server tools