endurium endurium - 6 months ago 29
SQL Question

How to list role members in SQL Server 2008 R2

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

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.