列出 SQL Server 角色成员

huangapple go评论70阅读模式
英文:

List SQL Server role members

问题

I'm trying to list specific users having server role 'sysadmin' or 'securityadmin'.

My goal is to get:

name    role       type_description   is_disabled
-------------------------------------------------
User1   sysadmin   windows_group         0

I have 2 issues:

  1. I failed to show the role name 2
  2. The instance is case sensitive and DOM\MSSQL_Admins will be displayed (and it shouldn't be)

This is my code:

SELECT   
    name, type_desc, is_disabled
FROM     
    master.sys.server_principals 
WHERE    
    (IS_SRVROLEMEMBER ('sysadmin', name) = 1
     OR IS_SRVROLEMEMBER ('securityadmin', name) = 1)
    AND name NOT IN ('sa', LOWER('dom\mssql_admins'), 'dom\netbackup_mssql', 'dom\userX',
                     'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt',
                     'NT SERVICE\MSSQLSERVER',
                     'NT SERVICE\SQLSERVERAGENT', 'dom\SQL-TASK')
    AND name NOT LIKE '%$%'
ORDER BY 
    name
英文:

I'm trying to list specific users having server role 'sysadmin' or 'securityadmin'.

My goal is to get:

name    role       type_description   is_disabled
-------------------------------------------------
User1   sysadmin   windows_group         0

I have 2 issues:

  1. I failed to show the role name 2
  2. The instance is case sensitive and DOM\MSSQL_Admins will be displayed (and it shouldn't be)

This is my code:

SELECT   
    name, type_desc, is_disabled
FROM     
    master.sys.server_principals 
WHERE    
    (IS_SRVROLEMEMBER ('sysadmin', name) = 1
     OR IS_SRVROLEMEMBER ('securityadmin', name) = 1)
    AND name NOT IN ('sa', LOWER('dom\mssql_admins'), 'dom\netbackup_mssql', 'dom\userX',
                   	 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt',
                     'NT SERVICE\MSSQLSERVER',
                     'NT SERVICE\SQLSERVERAGENT', 'dom\SQL-TASK')
    AND name NOT LIKE '%$%'
ORDER BY 
    name

答案1

得分: 1

尝试使用sys.server_role_members表和sys.server_principals表来获取所需的信息:

SELECT
    p.name AS [name],
    r.name AS [role],
    p.type_desc AS [type_description],
    p.is_disabled
FROM
    sys.server_role_members m
    INNER JOIN sys.server_principals p ON m.member_principal_id = p.principal_id
    INNER JOIN sys.server_principals r ON m.role_principal_id = r.principal_id
WHERE
    (
        r.name = 'sysadmin'
        OR r.name = 'securityadmin'
    )
    AND (
        LOWER(p.name) NOT IN (
            'sa',
            LOWER('dom\mssql_admins'),
            LOWER('dom\netbackup_mssql'),
            LOWER('dom\userX'),
            'NT SERVICE\SQLWriter',
            'NT SERVICE\Winmgmt',
            'NT SERVICE\MSSQLSERVER',
            'NT SERVICE\SQLSERVERAGENT',
            LOWER('dom\SQL-TASK')
        )
        OR p.name = 'DOM\MSSQL_Admins'
    )
    AND p.name NOT LIKE '%$%'
ORDER BY
    p.name;

sys.server_role_members表与sys.server_principals表连接两次:一次用于获取成员主体信息(p),一次用于获取角色主体信息(r)。

英文:

Try using the sys.server_role_members table and the sys.server_principals table to get the desired information :

SELECT
    p.name AS [name],
    r.name AS [role],
    p.type_desc AS [type_description],
    p.is_disabled
FROM
    sys.server_role_members m
    INNER JOIN sys.server_principals p ON m.member_principal_id = p.principal_id
    INNER JOIN sys.server_principals r ON m.role_principal_id = r.principal_id
WHERE
    (
        r.name = 'sysadmin'
        OR r.name = 'securityadmin'
    )
    AND (
        LOWER(p.name) NOT IN (
            'sa',
            LOWER('dom\mssql_admins'),
            LOWER('dom\netbackup_mssql'),
            LOWER('dom\userX'),
            'NT SERVICE\SQLWriter',
            'NT SERVICE\Winmgmt',
            'NT SERVICE\MSSQLSERVER',
            'NT SERVICE\SQLSERVERAGENT',
            LOWER('dom\SQL-TASK')
        )
        OR p.name = 'DOM\MSSQL_Admins'
    )
    AND p.name NOT LIKE '%$%'
ORDER BY
    p.name;

The sys.server_role_members table is joined with sys.server_principals twice: once to get the principal member information (p) and once to get the principal role information (r).

huangapple
  • 本文由 发表于 2023年7月3日 14:59:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76602500.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定