SELECT
[UserType] =
CASE membprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] =
ulogin.[name],
[Role] =
roleprinc.[name]
FROM
--Role/member
associations
sys.database_role_members AS
members
--Roles
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
--Role
members (database users)
JOIN sys.database_principals AS membprinc
ON membprinc.[principal_id]
= members.[member_principal_id]
--Login
accounts
LEFT JOIN sys.server_principals AS
ulogin
ON
ulogin.[sid] =
membprinc.[sid]
WHERE membprinc.[type]
IN ('S','U','G')
-- No need
for these system accounts
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
