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