Audit SQL Server Jobs

I don’t see a lot of questions or discussions around the use of SQL Server Audit. To me, SQL Server Audit is one of those features that doesn’t get enough love and attention. That’s why I’ve decided to take the time today to show how to use SQL Server Audit to audit SQL Server jobs.

We can use SQL Server Audit to track just about anything. In this post I will show you how to create an audit for SQL Server jobs. If anyone adds, deletes, or modifies a job you can track who did what, and when.

[I’m limiting the scope of this post to SQL Agent jobs only. Feel free to do the research regarding job steps and schedules. This post should help you get that done.]

We will start by identifying the objects in scope for the audit. When a user creates a job through SQL Server Management Studio (SSMS), the sp_add_job system procedure is executed. A little digging reveals the objects used by SSMS:

sp_add_job
sp_update_job
sp_delete_job

Each of those procedures will insert, update, and delete from the following table:

msdb.dbo.sysjobs

Note that a user (or 3rd party vendor) could try to update the sysjobs table directly, so it’s important that we scope our audit to all possibilities. That’s why we will audit both the procedures and the underlying table.

Now that we know the objects, we will start to create the audit. First, we will want to create a Server Audit. This is the “kitchen sink” for SQL Server Audit, as it catches everything and determines where to send the event output. For this example I will keep things simple and output everything to a file on my laptop. You can see my settings here:

Next, we want to create a Database Audit Specification for the msdb database. We will limit the focus to the procedures and table listed earlier. The result will look like this:

Now, enable both the database specification and the server audit. Otherwise you won’t capture any events or have them logged for output.

Now we are ready to test. Let’s use SSMS to create a job. Here’s a simple example, no steps or schedules, just a name:

Next, check the audit log to see the events:

As expected, it captured the execution of the sp_add_job stored procedure. It also captured the insert statement that the sp_add_job calls.

Let’s finish the test. We will modify then delete the job using SSMS. The audit log will now look like this:

The delete is at the top, and the update is in focus. We have captured both the execution of the procedures as well as the update and delete to the sysjobs table.

Summary

I think SQL Server Audit is a great feature. But it can take a lot of clicks to get it right. That’s true for a lot of Microsoft products and features though. Microsoft is great at providing a framework to get the job done. I’ve often likened it to a tinker set. You get all the pieces, but you still need to put it together.

But the #hardtruth here is that you shouldn’t need to hire an expensive DBA to click 1,000 times in SSMS to configure an audit. The good news is that the Azure version of audit is much easier to use.

I’m hopeful that the simplicity found in Azure will make its way to the Earthed version.

Someday.