SQL Server Principals & Roles Permission Audit

Thanks to the expansion of catalog views in SQL Server 2005, managing users and permissions has become much easier. Specifically, if you largely manage users by way of roles, rather than per-object permissions, SQL exposes this relationship in a single view, which can produce a report of what principal is part of what role. Check out the query:

select db_name() [database_name]
        , dbpm.name, dbpm.type_desc, dbpm.create_date, dbpm.modify_date
        , dbpr.name, dbpr.type_desc
from sys.database_role_members dbrm
left join sys.database_principals dbpm
        on dbrm.member_principal_id = dbpm.principal_id
left join sys.database_principals dbpr
        on dbrm.role_principal_id = dbpr.principal_id
order by
        dbpm.name, dbpr.name

This is handily fantastic and works well when you’re just managing access to a single database. What about if you want to audit logins for the entirety of the database instance? Unfortunately, we have to rely on database cursors to build a query and execute it. The concept is easy, but the details can be a little tricky to manage. Here’s what I came up with:

declare @cmd nvarchar(max) = '', @dbname sysname
 
declare csr_dbname cursor for
select quotename(name) from sys.databases where state = 0
 
open csr_dbname
 
fetch next from csr_dbname
into @dbname
 
while @@fetch_status = 0
begin
        set @cmd = @cmd + '
select '''+@dbname+''' [database_name]
, dbpm.name [principal_name], dbpm.type_desc [principal_type], dbpm.create_date [principal_create], dbpm.modify_date [principal_modify]
, dbpr.name [role_name], dbpr.type_desc [role_type]
from '+@dbname+'.sys.database_role_members dbrm
left join '+@dbname+'.sys.database_principals dbpm
        on dbrm.member_principal_id = dbpm.principal_id
left join '+@dbname+'.sys.database_principals dbpr
        on dbrm.role_principal_id = dbpr.principal_id
        union all
        '
 
        fetch next from csr_dbname
        into @dbname
 
end
 
close csr_dbname
deallocate csr_dbname
 
set @cmd = left(@cmd, len(@cmd) - 12)
 
exec(@cmd)

There are a couple hacky things in this solution that could be factored out, but I’m keeping it simple. Here’s the walkthrough:

• Line 1: Declare and initialize necessary variables. Note that we’re combining declaration and initialization in a single step. If you’re not using SQL Server 2008, you’ll have to separate the initialization with a distinct “set @cmd = ”” command
• Line 3-4: Declare the query which defines the cursor. On line 4, we limit state = 0, because otherwise we might get databases in restoring or offline mode. Additionally, QUOTENAME() is used to ensure the input is sanitized for string concatenation. If you wanted to limit the scope of your databases, do it here.
• Line 6-12: Standard cursor setup
• Line 13-23: Build the query we’ll eventually use to scan all databases. We inject the database name from the cursor into the query to three-part qualify it, effectively neutralizing the current database context. Also, there’s a “union all” at the end that we’ll need to take care of later on
• Line 25-31: Standard cursor tear-down
• Line 33: Chuck the last “union all” from the query, lest it be dangling and we receive a parse error. If we were targeting re-usability, it would be smart to put the “union all” in a separate variable, rather than calculating the length of a literal
• Line 35: Execute the  query. We could optionally print the results here, but they’re typically longer than what Management Studio will display before truncating

It’s all quite simple and easy to toss into a stored procedure if you want to audit on a regular basis. Setting up a SQL Server Agent job to run monthly and return the results in an email would be straightforward enough. One of the issues I ran into and did not address was the sorting, which you may have noticed is absent in the extended script. This could be rectified by using “exec insert” notation, thought the results aren’t always dependable.