如何识别Server中的所有超级用户？

Question

对于安全审核，我需要识别所有超级用户并记录所执行的每一项活动。最好的方法？

所谓“超级用户”，我指的是sysadmin、serveradmin、securityadmin、processadmin和dbcreator。

Answer 1

您可以使用以下代码：

SELECT   name,type_desc,is_disabled, 
         (IS_SRVROLEMEMBER ('sysadmin',name)) AS ISSRVROLE
FROM     master.sys.server_principals 
-- WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name ;

sys.server_principals -为每个服务器级主体包含一行。

IS_SRVROLEMEMBER -指示Server登录名是否为指定服务器角色的成员。

您还需要检查sysadmin-equivalent CONTROL SERVER对sys.server_permissions中的服务器主体的权限。

IS_SRVROLEMEMBER可以返回：

• 0-登录不是角色的成员。
• 1-登录是角色的成员。
• 空角色或登录无效，或者您没有查看角色成员资格的权限。

Answer 2

您可以使用下面的代码获取详细信息：

WITH cte_SuperUser (name, role, type_desc)
AS (SELECT
  PRN.name,
  srvrole.name AS [role],
  Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT
  *
FROM sys.server_principals
WHERE type_desc = 'SERVER_ROLE') srvrole
  ON srvrole.Principal_id = membership.Role_principal_id
RIGHT JOIN sys.server_principals PRN
  ON PRN.Principal_id = membership.member_principal_id
WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE')
AND PRN.is_disabled = 0
UNION ALL
SELECT
  p.[name],
  'ControlServer',
  p.type_desc AS loginType
FROM sys.server_principals p
JOIN sys.server_permissions Sp
  ON p.principal_id = sp.grantee_principal_id
WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G')
SELECT
  name,
  Type_Desc,
  CASE
    WHEN [public] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'Public',
  CASE
    WHEN [sysadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'SysAdmin',
  CASE
    WHEN [securityadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'SecurityAdmin',
  CASE
    WHEN [serveradmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'ServerAdmin',
  CASE
    WHEN [setupadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'SetupAdmin',
  CASE
    WHEN [processadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'ProcessAdmin',
  CASE
    WHEN [diskadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'DiskAdmin',
  CASE
    WHEN [dbcreator] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'DBCreator',
  CASE
    WHEN [bulkadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'BulkAdmin',
  CASE
    WHEN [ControlServer] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'ControlServer'
FROM cte_SuperUser
PIVOT
(
COUNT(role) FOR role IN ([public], [sysadmin], [securityadmin], [serveradmin], [setupadmin],
[processadmin], [diskadmin], [dbcreator], [bulkadmin], [ControlServer])
) AS pvt
WHERE Type_Desc NOT IN ('SERVER_ROLE')
ORDER BY name, type_desc
GO

Answer 3

记录SQL服务器上的每个活动并不是“廉价”操作。

您可以使用数据库审核来跟踪特定类型的操作(那些需要管理优先级的操作)。这样，你一开始就不用费心去识别那些用户了。

否则，您可以使用前面的答案中提供的查询来跟踪具有管理级别的用户，然后运行扩展事件或跟踪来捕获这些用户所做的每一项操作。请记住，这可能会影响性能，因为SQL必须跟踪和保存每个事务(特别是如果使用跟踪)。