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

How to list role members in SQL Server 2008 R2

提问by endurium

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

我正在使用以下 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

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

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

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_membersactually contains all members of a role?

我想我还应该问表是否sys.database_role_members真的包含角色的所有成员？

回答by endurium

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.

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

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

以下查询复制了 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

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

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

回答by bigRon97

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

回答by Vignesh Kumar A

Try this

尝试这个

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

(Or)

（或者）

SELECT p.name, o.name, d.*
FROM sys.database_principals AS p
JOIN sys.database_permissions AS d ON d.grantee_principal_id = p.principal_id
JOIN sys.objects AS o ON o.object_id = d.major_id