SQL Server 2008 Auditing - For SQL Jobs (Delete and Create)

SQL Server 2008 Auditing

I recently got presented with a challenge: How do you monitor people creating or deleting a job.  just be able to monitor and find out who created or deleted the jobs and what time?

What actually is SQL Server Auditing??

It is a secure means to track who created a schema, table, SQL server agent jobs etc..
If someone makes changes to a table schema, or has created a new one it is easy to track those and make a record without notifying the user making the changes.

How to Audit SQL Server Agent Jobs??

Step 1:-  Open your database with log in name as "sa" as you will require administrator rights to create the audit feature

Step 2:-  Expand "Security", Right click on "Audit" and select "New Audit".

Step 3:- Enter the Audit name. Here in example the Audit name is taken as "SqlAgentObjectAccess_Audit"

Step 4:- Let the queue delay be 1000

Step 5:- Enter Audit destination as "Application Log" and Click "OK"

See the screenshot for reference - 

You can Also use the below T-SQl Command to create the audit feature

use master

GO  

-- Create a Server Audit to log all audit events to Windows Application Log  

CREATE SERVER AUDIT [SqlAgentObjectAccess_Audit]  

TO APPLICATION_LOG 

 WITH 

 (    QUEUE_DELAY = 1000 

       ,ON_FAILURE = CONTINUE 

         ,AUDIT_GUID = 'e1f7d882-b26e-4b70-bc03-87af197eb7de' 

  ) 

 ALTER SERVER AUDIT [SqlAgentObjectAccess_Audit] WITH (STATE = ON) 

 GO


Note that by default SQL Server Audits are disabled when they are created, which means nothing will be written to your log until until it is enabled. This can be done just by expanding the "Audit" and then right clicking on the new audit created and choosing “Enable”.

NEXT STEP:-

The next bit is to set up the Database Audit on the SQL Agent Jobs table

Execute the below T-SQl command to set the Database Audit on the SQL Agent Jobs table

-- Create Database Audit specification to audit all execute calls initiated by dbo 

USE [msdb]

GO

CREATE DATABASE AUDIT SPECIFICATION [SqlAgentObjectAccess_Audit_MSDB]

FOR SERVER AUDIT [SqlAgentObjectAccess_Audit]

ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [SQLAgentUserRole]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [SQLAgentUserRole])

WITH (STATE = ON)

GO

Testing desired changes for Audit feature

Create a job and then delete the job. Go to control panel -> System and Security -> Open Windows Event log viewer -> Open Application Log.

You will see one log entry for sp_add_job call and another log entry for sp_delete_job call


Note: The SQL Server Audit feature does not work with SQL Server standard edition