代码之家  ›  专栏  ›  技术社区  ›  user23192546

在数据库中查找不同的角色和成员[重复]

  •  -2
  • user23192546  · 技术社区  · 1 年前

    我正在使用以下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 是数据库角色-没有的用户成员 数据库数据读取器 在查询中列出。

    为什么?如何列出数据库角色的用户成员?

    我想我也应该问一下桌子 sys.database_role_members 实际上包含一个角色的所有成员?

    0 回复  |  直到 11 年前
        1
  •  37
  •   endurium    11 年前

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

    当我查询出角色成员时,我正在将输出与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将角色扩展到其用户成员中。

        2
  •  22
  •   Community CDub    1 年前

    这是另一种方式

    SELECT dp.name as RoleName, us.name as UserName 
    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
    
        3
  •  3
  •   Vignesh Kumar A    11 年前

    试试这个

    ;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
    

    (或者)

    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