How to list role members in SQL Server 2008 R2

Question

I'm using the following T-SQL to obtain role members from my SQL Server 2008 R2 database:

select rp.name as database_role, mp.name as database_user from sys.database_role_members drm   join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)   join sys.database_principals mp on (drm.member_principal_id = mp.principal_id) order by rp.name 

When I examine the output I notice that the only role members listed for db_datareader are db roles - no user members of db_datareader are listed in the query.

Why is that? How can I also list the user members of my db roles?

I guess I should also ask whether the table sys.database_role_members actually contains all members of a role?

Answer 1

I've worked out what's going on.

When I queried out the role members I was comparing the output with what SSMS listed as role members in the role's properties dialog - this included users as well as roles, but the users weren't being listed by the query as listed in my question. I turns out that when listing role members, SSMS expands members that are roles to display the members of those roles.

The following query replicates the way in which SSMS lists role members:

WITH RoleMembers (member_principal_id, role_principal_id)  AS  (   SELECT     rm1.member_principal_id,     rm1.role_principal_id   FROM sys.database_role_members rm1 (NOLOCK)    UNION ALL   SELECT     d.member_principal_id,     rm.role_principal_id   FROM sys.database_role_members rm (NOLOCK)    INNER JOIN RoleMembers AS d     ON rm.member_principal_id = d.role_principal_id ) select distinct rp.name as database_role, mp.name as database_userl from RoleMembers drm   join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)   join sys.database_principals mp on (drm.member_principal_id = mp.principal_id) order by rp.name 

The above query uses a recursive CTE to expand a role into it's user members.

Answer 2

Here is another way

SELECT dp.name , us.name   FROM sys.sysusers us right  JOIN  sys.database_role_members rm ON us.uid = rm.member_principal_id JOIN sys.database_principals dp ON rm.role_principal_id =  dp.principal_id