Auditing SQL Agent job creation and deletion


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]
   2: GO
   4: -- Create a Server Audit to log all audit events to Windows Application Log
   5: CREATE SERVER AUDIT [SqlAgentObjectAccess_Audit]
   7: WITH
   8: (    QUEUE_DELAY = 1000
  10:     ,AUDIT_GUID = 'e1f7d882-b26e-4b70-bc03-87af197eb7de'
  11: )
  12: ALTER SERVER AUDIT [SqlAgentObjectAccess_Audit] WITH (STATE = ON)
  13: GO
  16: USE [msdb]
  17: GO
  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
  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)
  28: GO


After turning on auditing using above T-SQL script, try creating a test SQL Agent job and delete that job

   1: USE msdb
   2: GO
   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

Comments (5)

  1. Chuck Hottle says:

    That's pretty neat, though it would be nice if it was logged to a table instead of the event log.

  2. Ludwig says:

    Cool. What about someone disabling the job or changing it. Can that be tracked as well?

  3. Ludwig says:

    Cool. What about someone disabling the job or changing it. Can that be tracked as well?

  4. you can audit sp_update_job to track someone disabling a job

  5. Asaf says:

    Anyone who consider this as a solution needs to be aware of SQL Server Standard edition doesn't support auditing.

    Msg 33075, Level 16, State 1, Line 4

    Auditing is not available in this edition of SQL Server. For more information about feature support in the editions of SQL Server, see SQL Server Books Online.

Skip to main content