SELECT
p.name as ‘LoginName’, pp.name as ‘Role’
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
Order By p.name, pp.name