英文:
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:
- I failed to show the role name 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:
- I failed to show the role name 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
).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论