The sys.database_role_members view contains two columns one for the role id and one for the member id creating a relationship between the role and the user. When we join this data to the sys.database_principals view we can retrieve the friendly name of the role and using another join to get the User name.

This will only return roles and the users associated if the role is not empty of members.

select as 'Role Name', as 'User' from sys.database_role_members rm
inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id
inner join sys.database_principals mp on rm.member_principal_id = mp.principal_id
Share This