查询Microsoft SQL Server数据库角色权限变更历史的方法

默认情况下，SQL Server的系统视图仅存储当前生效的权限状态，不保留权限变更的历史记录（包括变更时间、操作人）。要获取这类历史信息，需依赖日志审计或专门的监控配置，以下是可行方案：

一、通过事务日志回溯历史（无提前配置时的应急方案） #

如果数据库采用完整恢复模式且事务日志未被备份截断，可以通过系统函数fn_dblog解析日志，提取角色权限的GRANT/REVOKE/DENY操作记录。

查询语句示例： #

SELECT
    -- 解析目标角色名称
    SUBSTRING(OperationDescription, CHARINDEX('TO ', OperationDescription) + 3, LEN(OperationDescription) - CHARINDEX('TO ', OperationDescription) - 2) AS [Role],
    -- 解析具体权限内容
    CASE
        WHEN OperationDescription LIKE 'GRANT%' THEN SUBSTRING(OperationDescription, CHARINDEX('GRANT ', OperationDescription) + 6, CHARINDEX(' TO ', OperationDescription) - CHARINDEX('GRANT ', OperationDescription) - 6)
        WHEN OperationDescription LIKE 'REVOKE%' THEN SUBSTRING(OperationDescription, CHARINDEX('REVOKE ', OperationDescription) + 7, CHARINDEX(' TO ', OperationDescription) - CHARINDEX('REVOKE ', OperationDescription) - 7)
        WHEN OperationDescription LIKE 'DENY%' THEN SUBSTRING(OperationDescription, CHARINDEX('DENY ', OperationDescription) + 5, CHARINDEX(' TO ', OperationDescription) - CHARINDEX('DENY ', OperationDescription) - 5)
    END AS [Permission],
    -- 变更时间
    [TransactionTime] AS [Modify_Date],
    -- 操作人账号
    s.login_name AS [Grantor Name],
    -- 操作类型说明
    CASE 
        WHEN OperationDescription LIKE 'GRANT%' THEN '(授予权限)' 
        WHEN OperationDescription LIKE 'REVOKE%' THEN '(撤销权限)' 
        WHEN OperationDescription LIKE 'DENY%' THEN '(拒绝权限)' 
    END AS [Operation]
FROM
    fn_dblog(NULL, NULL) d
JOIN
    sys.dm_tran_session_transactions st ON d.transaction_id = st.transaction_id
JOIN
    sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE
    d.Operation IN ('LOP_GRANT_PERM', 'LOP_REVOKE_PERM', 'LOP_DENY_PERM')
    AND OperationDescription LIKE '%TO %' -- 过滤针对角色/用户的权限操作
ORDER BY
    TransactionTime DESC;

注意事项： #

• 仅适用于完整恢复模式，且事务日志未被自动截断或备份覆盖；
• 日志解析的字符串逻辑需根据实际权限语句调整，部分复杂权限可能解析不准确；
• 无法获取已被清理的历史日志记录。

二、通过SQL Server Audit持续记录权限变更（推荐方案） #

要长期稳定获取权限变更历史，建议配置SQL Server Audit，专门捕获数据库角色的权限操作。

1. 创建审计对象与规范 #

-- 创建服务器级审计（指定日志存储路径，按需修改）
CREATE SERVER AUDIT [RolePermissionAudit]
TO FILE (FILEPATH = 'D:\SQL_Audit_Logs\', MAXSIZE = 100 MB)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

-- 启用审计
ALTER SERVER AUDIT [RolePermissionAudit] WITH (STATE = ON);

-- 创建数据库级审计规范（替换[YourDatabase]为目标库名）
CREATE DATABASE AUDIT SPECIFICATION [RolePermissionAuditSpec]
FOR SERVER AUDIT [RolePermissionAudit]
ADD (GRANT_DATABASE_PERMISSION ON DATABASE::[YourDatabase] TO PUBLIC),
ADD (REVOKE_DATABASE_PERMISSION ON DATABASE::[YourDatabase] TO PUBLIC),
ADD (DENY_DATABASE_PERMISSION ON DATABASE::[YourDatabase] TO PUBLIC)
WITH (STATE = ON);

2. 查询审计日志获取历史记录 #

SELECT
    target_name AS [Role],
    -- 转换权限操作描述为可读格式
    CASE action_id_desc
        WHEN 'GRANT_DATABASE_PERMISSION' THEN SUBSTRING(additional_information, CHARINDEX('permission_name":"', additional_information)+17, CHARINDEX('"', additional_information, CHARINDEX('permission_name":"', additional_information)+17)-CHARINDEX('permission_name":"', additional_information)-17)
        WHEN 'REVOKE_DATABASE_PERMISSION' THEN SUBSTRING(additional_information, CHARINDEX('permission_name":"', additional_information)+17, CHARINDEX('"', additional_information, CHARINDEX('permission_name":"', additional_information)+17)-CHARINDEX('permission_name":"', additional_information)-17)
        WHEN 'DENY_DATABASE_PERMISSION' THEN SUBSTRING(additional_information, CHARINDEX('permission_name":"', additional_information)+17, CHARINDEX('"', additional_information, CHARINDEX('permission_name":"', additional_information)+17)-CHARINDEX('permission_name":"', additional_information)-17)
    END AS [Permission],
    event_time AS [Modify_Date],
    session_server_principal_name AS [Grantor Name],
    CASE action_id_desc
        WHEN 'GRANT_DATABASE_PERMISSION' THEN '(授予权限)'
        WHEN 'REVOKE_DATABASE_PERMISSION' THEN '(撤销权限)'
        WHEN 'DENY_DATABASE_PERMISSION' THEN '(拒绝权限)'
    END AS [Operation]
FROM
    sys.fn_get_audit_file('D:\SQL_Audit_Logs\RolePermissionAudit_*.sqlaudit', DEFAULT, DEFAULT)
WHERE
    action_id IN ('GDR', 'RVD', 'DND') -- 对应授予/撤销/拒绝数据库权限的操作码
ORDER BY
    event_time DESC;

查询结果示例（匹配需求格式）： #

三、扩展事件方案（轻量级监控） #

若不想使用SQL Server Audit，也可通过扩展事件创建会话，捕获权限变更操作，从事件文件或实时会话中提取记录，适合精细化监控场景。

内容的提问来源于stack exchange，提问作者Jeremy F.