Auditing can be used to track and log events that occur on system. You can read more about auditing here.
SQL Agent jobs are added / removed using stored procedure interface sp_add_job and sp_delete_job. Customers have asked us how we could track user who has created a job / deleted a specific job. You could accomplish this using SQL Server Auditing feature. You can turn on Auditing to track and log stored procedure execution requests to file log / windows event log.
Following sample T-SQL code can be used to track and log execution requests to sp_add_job and sp_delete_job stored procedures
1: USE [master]
4: -- Create a Server Audit to log all audit events to Windows Application Log
5: CREATE SERVER AUDIT [SqlAgentObjectAccess_Audit]
6: TO APPLICATION_LOG
8: ( QUEUE_DELAY = 1000
9: ,ON_FAILURE = CONTINUE
10: ,AUDIT_GUID = 'e1f7d882-b26e-4b70-bc03-87af197eb7de'
12: ALTER SERVER AUDIT [SqlAgentObjectAccess_Audit] WITH (STATE = ON)
16: USE [msdb]
18: -- Create Database Audit specificication to audit all execute calls initiated by dbo
19: -- Note: This is just a sample to audit execute requests done to sp_add_job and sp_delete_job stored proc API.
20: -- similair approach can be done to audit execute calls to scheule / operators / alerts stored procedure API
21: CREATE DATABASE AUDIT SPECIFICATION [SqlAgentObjectAccess_Audit_MSDB]
22: FOR SERVER AUDIT [SqlAgentObjectAccess_Audit]
23: ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
24: ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [SQLAgentUserRole]),
25: ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
26: ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [SQLAgentUserRole])
27: WITH (STATE = ON)
After turning on auditing using above T-SQL script, try creating a test SQL Agent job and delete that job
1: USE msdb
3: -- Create a test job 1
4: EXEC sp_add_job 'testjob1'
6: -- Delete test job 1
7: EXEC sp_delete_job @job_name='testjob1'
Open Windows Event log viewer, Open “Application Log”; refresh event viewer if User interface has not reloaded all new event entries. You will see one log entry for sp_add_job call and another log entry for sp_delete_job call
Stored procedure API for creating and dropping job is called by SSMS user interface , Powershell, SMO API. If Auditing is enabled for stored procedure execution, you can capture information on who added / dropped a SQL Agent job