SQL Server查询sysadmin/securityadmin角色成员遇问题求助

问题1：显示角色名称 #

原查询仅查询了服务器主体信息，未关联角色表，因此无法返回角色名称。需通过sys.server_role_members关联角色对应的主体记录，获取角色名称。

问题2：区分大小写导致过滤失效 #

实例区分大小写时，直接使用name NOT IN (...)会因大小写不匹配导致过滤失败。可通过统一转换为小写（或大写）进行比较，规避大小写敏感问题。

修改后的查询代码 #

SELECT   
    sp.name AS principal_name,
    role_princ.name AS role,
    sp.type_desc,
    sp.is_disabled
FROM     
    master.sys.server_principals sp
JOIN master.sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
JOIN master.sys.server_principals role_princ ON srm.role_principal_id = role_princ.principal_id
WHERE    
    role_princ.name IN ('sysadmin', 'securityadmin')
    AND LOWER(sp.name) NOT IN ('sa', LOWER('dom\mssql_admins'), LOWER('dom\netbackup_mssql'), LOWER('dom\userX'),
                               LOWER('NT SERVICE\SQLWriter'), LOWER('NT SERVICE\Winmgmt'),
                               LOWER('NT SERVICE\MSSQLSERVER'),
                               LOWER('NT SERVICE\SQLSERVERAGENT'), LOWER('dom\SQL-TASK'))
    AND sp.name NOT LIKE '%$%'
ORDER BY 
    sp.name

代码说明 #

• 两次JOIN关联角色表，明确获取用户所属的服务器角色名称
• 用LOWER()函数统一转换主体名称为小写，解决大小写敏感导致的过滤失效问题
• 直接通过角色名称过滤目标角色，替代原查询的IS_SRVROLEMEMBER函数，逻辑更直观

内容的提问来源于stack exchange，提问作者Purclot