Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Auditing SQL Agent job creation and deletion

Auditing SQL Agent job creation and deletion

  • Comments 5

 

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
   3:  
   4: -- Create a Server Audit to log all audit events to Windows Application Log
   5: CREATE SERVER AUDIT [SqlAgentObjectAccess_Audit]
   6: TO APPLICATION_LOG
   7: WITH
   8: (    QUEUE_DELAY = 1000
   9:     ,ON_FAILURE = CONTINUE
  10:     ,AUDIT_GUID = 'e1f7d882-b26e-4b70-bc03-87af197eb7de'
  11: )
  12: ALTER SERVER AUDIT [SqlAgentObjectAccess_Audit] WITH (STATE = ON)
  13: GO
  14:  
  15:  
  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
  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)
  28: GO
  29:  

 

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'
   5:  
   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

 

image

 

image

 

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

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
  • That's pretty neat, though it would be nice if it was logged to a table instead of the event log.

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

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

  • you can audit sp_update_job to track someone disabling a job

  • 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.

Page 1 of 1 (5 items)