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
...