Click on the banner to learn about and purchase my database training on Azure

How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server

Views: 4.829 views
This post is the 2 part of 20 in the series. Security and Audit
Reading Time: 3 minutes

Hello guys,
Good day.

Today I come to you, the solution of some questions that I asked myself in the environments in which I work, but I could not answer:
- Who created / changed / deleted a specific job?
- I accidentally deleted a Job .. How do I recreate it quickly, without needing to restore backup?
- Someone disabled a particular Job. Who was it?
- Someone changed the job description. I need to identify who it was.

To answer these questions, we will create a way to monitor the creation, modification, and deletion of Jobs in SQL Server by triggering the msdb.dbo.sysjobs table.

Creating the history table

Before creating the trigger that will retrieve the information, we will need to create the table that will store the information and create the history.

Trigger creation

Now that we create the table that will have our information, let's create the trigger that will populate it.

Querying the information

Now that our trigger is on, make some changes to your Jobs and see how the information is saved. It is possible to identify the date / time the event was triggered, the user who performed the event and even the query that was executed in the database, either for Job creation, deletion or modification.

SQL Server - Job Audit

See you!