SQL Auditing on MSDB jobs

Hi Folks,

I could see many of my mates requesting me to find out who has created/deleted jobs inside MSDB Database. Hence I thought of writing an article on the same.

There are 2 ways to achieve this.

1) Server Auditing

2)Triggers

It is not recommended to make use of triggers on system tables unless you are running on SQL Server 2000/2005 or if you are running on Standard edition on higher versions

Here is a summary of the SQL Server Audit support in the different editions:
 Edition  SQL Server 2008 and 2008 R2  SQL Server 2012 and 2014
 Enterprise  Server- and database-level Server- and database-level
 Evaluation  Server- and database-level Server- and database-level
 Developer  Server- and database-level Server- and database-level
 Datacenter  Server- and database-level N/A
 Business Intelligence  None  Server-level
 Standard  None  Server-level
 Web  None  Server-level
 Express  None  Server-level

In this article we are going to explore Server Auditing.

1)Right Click on Audit inside the security & create one for yourself
Here give an appropriate name for your Audit & give a file path where you want your auditing files to get saved for.

2)once done kindly enable the Auditing.

3)SQL Agent jobs are added / removed using stored procedure interface sp_add_job and    sp_delete_job hence we are creating auditing on those 2 stored procedures inside MSDB
Go to MSDB right click on Security & then select Database Audit specification.

4)kindly create auditing on the above stored procedures by making use of below diagram

5)once done don't forget to enable the auditing.

Now you can create jobs both as sysadmin as well as non sysadmin & see whether it is getting tracked inside our Audit file. Below is the statement where we just to need pass the path of what we made use of at the time of Audit creation

SELECT event_time,server_principal_name,database_principal_name,statement FROM sys.fn_get_audit_file ('D:\DBA\MSDB_SYSJOBS_AUDITING_78B18D4C-493C-40C5-BA3C-3C05CD7C8CD4_0_131643011827530000.sqlaudit',default,default)


As you can see you will the get the job_id's instead of the job name so just pass the job_id in where clause to sysjobs to find the relevant job name

There you go now we achieved our goal. Happy Reading.

Comments