sql - 如何在 SQL Server 2008 R2 中列出角色成员

Question

我正在使用以下 T-SQL 从我的 SQL Server 2008 R2 数据库中获取角色成员:

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

当我检查输出时，我注意到为 db_datareader 列出的唯一角色成员是数据库角色 - 没有 db_datareader 的用户成员列在查询中。

这是为什么？我怎样才能列出我的数据库角色的用户成员？

我想我也应该问一下是否表 sys.database_role_members实际上包含一个角色的所有成员？

Answer 1

我已经弄清楚发生了什么。

当我查询角色成员时，我正在将输出与 SSMS 在角色属性对话框中列为角色成员的内容进行比较 - 这包括用户和角色，但查询并未列出我的问题中列出的用户。我发现在列出角色成员时，SSMS 会扩展作为角色的成员以显示这些角色的成员。

以下查询复制了 SSMS 列出角色成员的方式:

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

上述查询使用递归 CTE 将角色扩展为其用户成员。