利用 SQL Server Audit 审核哪些用户添加删除更新SQL Agent Job

有的时候我们需要下放权限给不用的用户，让他们自己能管理一部分SQL Agent Job，此时需要详细记录谁在什么时间修改了Job 甚至删除了Job, 我们可以使用SQL Server 的Audit帮助我们完成记录，参考下面的脚本，根据你的环境改变路径。

•  创建Audit脚本

USE [master]

GO
CREATE SERVER AUDIT [SQLAgentJobAudit]
TO FILE
( FILEPATH = N'd:logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '0d3c98d4-56ad-446c-b4c7-aff25ee4d140'
)

ALTER SERVER AUDIT [SQLAgentJobAudit] WITH (STATE = OFF)
GO

USE [msdb]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-JobAudit]
FOR SERVER AUDIT [SQLAgentJobAudit]
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_update_job] BY [dbo]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [dbo])
WITH (STATE = ON)

USE master
GO
ALTER SERVER AUDIT [SQLAgentJobAudit] WITH (STATE = ON)
GO

• 如何查看审核日志：

 

• T-SQL 查看：

SELECT * FROM sys.fn_get_audit_file ('D:logsSQLAgentJobAudit_0D3C98D4-56AD-446C-B4C7-AFF25EE4D140_0_131999586451030000.sqlaudit',default,default)

where statement like '%aa700430-5e68-45c6-9cc0-3c89215f5613%'