如何在 SQL Server 2008 R2 中列出角色成员 [英] How to list role members in SQL Server 2008 R2

问题描述

我正在使用以下 T-SQL 从我的 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

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

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?

我想我也应该问一下 sys.database_role_members 表是否真的包含了一个角色的所有成员?

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

推荐答案

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

I've worked out what's going on.

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

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.

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

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

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

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

这篇关于如何在 SQL Server 2008 R2 中列出角色成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！