SQL Server代理作业执行审计日志查询发邮件失败求助

需求 #

每15分钟将以下审计日志查询结果通过邮件发送：

select 
    event_time, server_principal_name, application_name, 
    database_name, statement 
from
    sys.fn_get_audit_file ('f:\audit\*' , null,null)
where 
    statement not like 'UPDATE STATISTICS%'
    and statement not like 'SELECT%'
    and dateadd(MINUTE, datediff(MINUTE, getutcdate(), current_timestamp), event_time)   
          > dateadd(minute, -15, getdate())
order by
    event_time desc

该查询在登录状态下运行正常，因此创建了如下SQL Server代理作业。

创建的SQL Server代理作业脚本 #

begin  
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'default',  
@recipients = '_dba@mymail.be',  
@subject = 'Audit', 
@body_format='HTML',
@query = N'select event_time, server_principal_name, application_name, database_name, statement  from sys.fn_get_audit_file ("f:\audit\*" , null,null)
where statement not like "UPDATE STATISTICS%"
and statement not like "SELECT%"
and DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) >dateadd(minute,-15, getdate())
ORDER BY event_time DESC',  
@attach_query_result_as_file = 1,  
@query_attachment_filename = 'auditResult.html'  
end

报错信息 #

作业执行失败，报错如下：

Executed as user: TOBACCO\MSA_SQL_Agent$. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step failed.

测试情况 #

• 将查询替换为select 1时，作业运行正常；
• 将查询替换为仅调用sys.fn_get_audit_file的语句时，作业失败；
• 在SSMS中直接运行该查询及sp_send_dbmail语句均正常；
• 仅作业运行时失败。

解决提示 #

1. 检查SQL Agent服务账户的文件系统权限：作业执行账户TOBACCO\MSA_SQL_Agent$需要对审计日志目录f:\audit\拥有读取权限。SSMS运行时使用当前登录用户权限，而SQL Agent作业使用自身服务账户，若该账户无目录读取权限，会导致无法访问审计文件。
2. 验证SQL权限：确保TOBACCO\MSA_SQL_Agent$账户拥有VIEW SERVER STATE权限，或者针对服务器审计的特定权限（如ALTER ANY SERVER AUDIT、VIEW ANY SERVER AUDIT），否则无法调用sys.fn_get_audit_file函数。
3. 修正查询中的字符串转义：在@query的N前缀字符串中，路径的引号应该用两个单引号转义，而非"。修改后的路径部分应为：sys.fn_get_audit_file (''f:\audit\*'' , null,null)，同理like条件里的字符串也需改为''UPDATE STATISTICS%''。
4. 指定查询执行的数据库：在sp_send_dbmail中添加@query_database = 'master'参数，因为sys.fn_get_audit_file属于master数据库下的系统函数，指定后可避免上下文数据库权限问题。

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