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

 

Auditing SQL Server User and Role Permissions for Databases


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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? I'm trying to audit permissions within the database itself.

Solution

The answer on how to do this depends on what version of SQL Server you are using. SQL Server 2005 introduced a new security model built around the concept of securables and while the old security tables were retained from 2000, they only report information that you would see in a SQL Server 2000 server. That means any of the newer securables, like schemas, certificates, asymmetric keys, even the database itself, will not be reported if you use the old SQL Server 2000 tables in SQL Server 2005 or above. So let's start with SQL Server 2000.

SQL Server 2000 - Using sysprotects

The sysprotects system table reports all of the permissions granted or denied in a given database. We'll need to join it with sysusers and sysobjects to get all the information we need. Here's an example query that only pulls information on objects (no CREATE TABLE permissions or anything else at the database level):

SELECT 
    su.name AS 'User'
  , CASE sp.protecttype
      WHEN 204 THEN 'GRANT w/ GRANT'
      WHEN 205 THEN 'GRANT'
      WHEN 206 THEN 'DENY' END AS 'Permission'
  , CASE sp.action
      WHEN 26 THEN 'REFERENCES'
      WHEN 193 THEN 'SELECT'
      WHEN 195 THEN 'INSERT'
      WHEN 196 THEN 'DELETE'
      WHEN 197 THEN 'UPDATE'
      WHEN 224 THEN 'EXECUTE' END AS 'Action'
  , so.name AS 'Object'
FROM sysprotects sp
  INNER JOIN sysusers su
    ON sp.uid = su.uid
  INNER JOIN sysobjects so
    ON sp.id = so.id
WHERE sp.action IN (26, 193, 195, 196, 197, 224) 
ORDER BY su.name, so.name; 

SQL Server 2000 - Using sp_helprotect

You're probably saying to yourself, what's with the case statements? Unfortunately, that's how the data is returned from sysprotects. Also, if you want to pull information back about column level permissions (should you know they exist), you'll have to deal with another column where permissions are stored as a bitmap. Not exactly the easiest thing to do. Thankfully, there is another way to handle this in SQL Server 2000, and that's with the system stored procedure sp_helprotect. The sp_helprotect stored procedure can take 4 parameters, but all of them are optional:

  • @name - Report all permissions for a given object
  • @username - Report all permissions for a given user
  • @grantorname - Report all permissions granted/denied by a particular user
  • @permissionarea - o for object permissions, s for statement permissions, and os for both

Basically, sp_helprotect will return all permissions unless you use one or more of the parameters to filter it down. For instance, some databases have more than 10,000 tables. If we're only interested in one, say LedgerStatement, we could do the following:

EXEC sp_helprotect @name = 'LedgerStatement';
 

SQL Server 2005/2008 - Using sys.database_permissions

Since sysprotects and sp_helprotect must act exactly as they did in SQL Server 2000, they aren't able to report on any of the newer securables found in SQL Server 2005 and above. Therefore, we must use a different method to get the same information out. To do this, Microsoft provides sys.database_permissions. It's a lot easier to use and provides readable entries to be able to determine what the permissions are. The one catch is that since it returns information on all objects, you'll still have to do a case statement, but just in order to use the correct function to retrieve the proper name of what was given permission against:

SELECT 
    USER_NAME(grantee_principal_id) AS 'User'
  , state_desc AS 'Permission'
  , permission_name AS 'Action'
  , CASE class
      WHEN 0 THEN 'Database::' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0; 
 

This query hits the main 3 types of securables: objects, schemas, and the database itself. Setting the minor_id = 0 filters out cases of column level permissions. If that is set to 1, then major_id is the unique ID for the column, which can be queried using sys.columns. There are other securables, too, such as the keys, XML schema collections, assemblies, etc., however, generally the query above will report on most of what an auditor might be interested in.

Next Steps


Last Update:


signup 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
Related Resources





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, January 31, 2014 - 1:42:11 PM - Tim Back To Top

I like you scripts, but I am trying to limit my results to Custom database roles, nor the users or default roles (public, dbo, db_datareader, etc.). How can I filter out the information to return only ther permissions for custom roles?


Tuesday, September 11, 2012 - 10:03:01 AM - K. Brian Kelley Back To Top

If it's a Windows group or user, check to see if the login has rights through a nested group. If it's a SQL Server based login, check to ensure it's not in the db_datareader role or that it or public doesn't have SELECT permissions at the schema or database level.

 


Friday, September 07, 2012 - 4:12:22 PM - Nitin Patil Back To Top

I have a question regarding to :

I'm right now managing the security for a new SQL log in. I do no thave server level access. I have created a database role and have added the SQL user to this role. I have added a securable to this role. This securable is a view. Now when I connect to the SQL server using this login/DB user, I should only see the view that I have added as securable to the role, where the user is mapped. But I see ALL the tables, and one view. Question is why I should see all the tablles when I  did not add them to securables?

Is it the login created with some defaults options?

Thanks,

Nitin

 

 

 


Wednesday, October 20, 2010 - 10:35:16 AM - Michael Back To Top
I am attempting to use the SQL 2000 script with the "EXEC sp_MSForEachDB 'USE [?]....' to poll each db, but I need the names of the db I'm polling. I have way to many db's to try to do this to each db individually. can someone assist, or can you help with this Brian?

Thanks in advance...


Learn more about SQL Server tools