Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Auditing SQL Server Permissions and Roles for the Server

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | More > Security
Problem

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?

Solution

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,
  
sid VARBINARY(85)
);

INSERT INTO #ServerRoles
(ServerRoleMemberNamesid)
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
UNION ALL
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: 6/29/2010


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, October 06, 2010 - 10:47:08 AM - Udhuman Read The Tip
Dear Sir,

 

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

 

 

Thanking You

 


Wednesday, October 20, 2010 - 3:34:39 PM - Yordan Georgiev Read The Tip
How could I say it ?

Thank you , Thank you , THANK YOU !!!


Wednesday, June 19, 2013 - 3:39:17 PM - David Potter Read The Tip

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.



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.