如何从fn_get_audit_file获取存储过程参数？SQL Server 2016 SP1

首先得明确核心原因：当你只针对表的INSERT/UPDATE/DELETE创建审核规范时，SQL Server Audit只会记录直接对表的操作事件——这些事件是存储过程内部触发的，不会关联到调用存储过程时传入的参数。同时默认审核配置也不会自动捕获存储过程执行的参数细节。

要拿到这些参数，你需要调整审核配置并解析日志里的JSON信息，具体步骤如下：

1. 扩展审核规范，捕获存储过程的执行事件 #

你需要在Database Audit Specification中添加针对目标存储过程的EXECUTE动作组，而不仅仅是表的增删改操作。这样Audit会记录存储过程的调用事件，而非仅底层的表操作。

示例创建/修改审核规范的SQL：

-- 假设你的审核规范名为TableAuditSpec，目标存储过程是dbo.YourProc
ALTER DATABASE AUDIT SPECIFICATION [TableAuditSpec]
ADD (EXECUTE ON OBJECT::dbo.YourProc BY PUBLIC)
WITH (STATE = ON);

2. 确保服务器审核启用了额外信息捕获 #

检查你的Server Audit是否开启了ADDITIONAL_INFORMATION选项（默认是开启的，但最好确认一下）：

SELECT name, additional_information
FROM sys.server_audits
WHERE name = 'YourServerAudit';

如果未启用，执行以下语句修改：

ALTER SERVER AUDIT [YourServerAudit]
WITH (ADDITIONAL_INFORMATION = ON);

3. 解析Audit日志中的JSON参数 #

存储过程执行事件的additional_information字段是JSON格式字符串，里面包含了调用时的参数名称和值。你可以用SQL Server的JSON函数提取这些信息。

示例1：提取单个参数 #

SELECT
  event_time,
  session_id,
  server_principal_name,
  object_name AS ProcedureName,
  statement AS ProcedureCall,
  -- 提取第一个参数的名称和值
  JSON_VALUE(additional_information, '$.parameters[0].name') AS Param1Name,
  JSON_VALUE(additional_information, '$.parameters[0].value') AS Param1Value,
  -- 提取第二个参数
  JSON_VALUE(additional_information, '$.parameters[1].name') AS Param2Name,
  JSON_VALUE(additional_information, '$.parameters[1].value') AS Param2Value
FROM sys.fn_get_audit_file('C:\YourAuditPath\Audit_*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'EX' -- 筛选存储过程执行事件
ORDER BY event_time DESC;

示例2：批量解析所有参数 #

如果存储过程有多个参数，用OPENJSON可以更灵活地解析所有参数：

SELECT
  a.event_time,
  a.session_id,
  a.object_name AS ProcedureName,
  p.name AS ParameterName,
  p.value AS ParameterValue
FROM sys.fn_get_audit_file('C:\YourAuditPath\Audit_*.sqlaudit', DEFAULT, DEFAULT) a
CROSS APPLY OPENJSON(a.additional_information, '$.parameters')
WITH (
  name NVARCHAR(128) '$.name',
  value NVARCHAR(MAX) '$.value'
) p
WHERE a.action_id = 'EX'
ORDER BY a.event_time DESC;

4. 关联表操作与存储过程调用 #

如果你需要把表的增删改事件和对应的存储过程调用关联起来，可以通过session_id和event_time字段匹配：

-- 获取存储过程执行事件及其参数
WITH ProcExecutions AS (
  SELECT
    event_time,
    session_id,
    object_name AS ProcedureName,
    additional_information
  FROM sys.fn_get_audit_file('C:\YourAuditPath\Audit_*.sqlaudit', DEFAULT, DEFAULT)
  WHERE action_id = 'EX'
)
-- 关联表操作事件
SELECT
  t.event_time AS TableActionTime,
  t.object_name AS TableName,
  t.action_id AS TableAction,
  p.ProcedureName,
  JSON_VALUE(p.additional_information, '$.parameters[0].value') AS ParamValue
FROM sys.fn_get_audit_file('C:\YourAuditPath\Audit_*.sqlaudit', DEFAULT, DEFAULT) t
JOIN ProcExecutions p ON t.session_id = p.session_id 
  AND t.event_time BETWEEN p.event_time AND DATEADD(SECOND, 5, p.event_time) -- 时间范围匹配
WHERE t.action_id IN ('INS', 'UPD', 'DEL')
ORDER BY t.event_time DESC;

注意事项 #

• 如果存储过程内部使用了动态SQL，参数可能不会出现在additional_information中，此时你需要添加STATEMENT_COMPLETED动作组来捕获动态SQL语句，但这会产生大量日志，需权衡性能影响。
• 确保你有足够的权限读取Audit文件和执行JSON函数（需要VIEW SERVER STATE权限）。

内容的提问来源于stack exchange，提问作者Novák Róbert