Auditing SQL Server User and Role Permissions for Databases

By: K. Brian Kelley   |   Updated: 2010-10-20   |   Comments (4)   |   Related: > Auditing and Compliance

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

• Read this related article: Auditing SQL Server Permissions and Roles
• Read these other security tips

About the author

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

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

View all my tips