Instantly share code, notes, and snippets.

Embed
What would you like to do?
List Database Users and Roles
SELECT DISTINCT
dbp.name AS DBUser,
dbp.type_desc AS UserType,
dbp.create_date AS CreateDate,
SUBSTRING(
(
SELECT DISTINCT ';' + roles2.name AS [text()]
FROM sys.database_principals AS dbp2
LEFT JOIN sys.database_role_members AS dbrm2
LEFT JOIN sys.database_principals AS roles2
ON dbrm2.role_principal_id = roles2.principal_id
ON dbp.principal_id = dbrm2.member_principal_id
WHERE dbp2.type IN ( 'S', 'U', 'G' )
AND dbp2.principal_id > 4
FOR XML PATH('')
),
2,
1000
) [Roles]
FROM sys.database_principals AS dbp
WHERE dbp.type IN ( 'S', 'U', 'G' )
AND dbp.principal_id > 4;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment