Audit database user & role mappings in SQL Server

This script provides you with a list, of the database user and database role mappings, for an entire SQL Server instance. The following system views are used;

sys.database_role_members
sys.database_principals

CREATE TABLE #user_groups
(
	username VARCHAR(50),
	[login_type] VARCHAR(50),
	database_name VARCHAR(50),
	database_group VARCHAR(50)
);
 
 
EXECUTE sp_MSForEachDB 'USE ?;
						INSERT INTO #user_groups
						SELECT  dp.name AS [user], 
								dp.type_desc AS [login_type],
								''?'',  
								groups.name AS [database_group]
						FROM sys.database_role_members drm
						INNER JOIN sys.database_principals dp
							ON dp.principal_id = drm.member_principal_id
						INNER JOIN sys.database_principals  groups
							ON groups.principal_id = drm.role_principal_id
						ORDER BY dp.name,
								 groups.name';
 
SELECT *
FROM #user_groups;
 
-- Clean up
DROP TABLE #user_groups;

Output will look something like below;

dbo	SQL_USER	master	db_owner
DOMAIN\SSRS01	WINDOWS_USER	master	RSExecRole
dbo	SQL_USER	tempdb	db_owner
dbo	SQL_USER	model	db_owner
...

4 Comments

  1. Norman says:

    This looks interesting and potentially handy for some login audits. But is there a way to deal with databases that have a hyphen in them? Like surrounding the db_name with [ ] or something?

    As is it crashes on my production server because of this.

    Thanks!
    Norman

  2. Rhys says:

    Hi Norman,

    Yes, surrounding with square bracket will do it.

    Cheers,

    Rhys

  3. Nice script. I have something similar you might like: http://sqlstudies.com/free-scripts/sp_dbpermissions/.

  4. Rhys says:

    Hi Kenneth,

    That looks great. I’ll give it a try.

    Cheers,

    Rhys

Leave a Reply

Current day month ye@r *