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.