利用 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:\logs\SQLAgentJobAudit_0D3C98D4-56AD-446C-B4C7-AFF25EE4D140_0_131999586451030000.sqlaudit',default,default)
where statement like '%aa700430-5e68-45c6-9cc0-3c89215f5613%'