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:

SQL Server Audit

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:

SQL Server Audit Database Specification msdb

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:

SQL Server Audit SQL Agent Job

Next, check the audit log to see the events:

SQL Server Audit Log File

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:

SQL Server Audit Log File

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.

6 thoughts on “Audit SQL Server Jobs”

  1. Also.. you can script all this stuff out from the wizard.. so you can save yourself a template of sorts and then just change some of the object names and this will save you lots of time in the future once you get it right once…

    For example this was created after using wizards and clicking on the script button

    USE [master]
    GO

    CREATE SERVER AUDIT [RockStarAudit]
    TO FILE
    ( FILEPATH = N’Y:Data’
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = ‘0d3c98d4-56ad-446c-b4c7-aff25ee4d140’
    )
    ALTER SERVER AUDIT [RockStarAudit] WITH (STATE = OFF)
    GO

    USE [msdb]

    GO

    CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20171012-145009]
    FOR SERVER AUDIT [RockStarAudit]
    ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
    ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [dbo])

    USE master
    GO

    ALTER SERVER AUDIT [RockStarAudit] WITH (STATE = ON)
    GO

    Reply
  2. It is a great explanation to Audit SQL Agent Job modifications, I would add [dbo].[sysschedules] to capture any changes in schedule

    — I have put down my Audit script in below

    USE [msdb]
    GO
    CREATE DATABASE AUDIT SPECIFICATION [MSD-Audit-Jobs]
    FOR SERVER AUDIT [file name ????] — Setup a New Server Audit from Server->Security->Audit — and point to that file name
    ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
    ADD (EXECUTE ON OBJECT::[dbo].[sp_update_job] BY [dbo]),
    ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
    ADD (DELETE ON OBJECT::[dbo].[sysjobs] BY [dbo]),
    ADD (INSERT ON OBJECT::[dbo].[sysjobs] BY [dbo]),
    ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [dbo]),
    ADD (DELETE ON OBJECT::[dbo].[sysschedules] BY [dbo]),
    ADD (INSERT ON OBJECT::[dbo].[sysschedules] BY [dbo]),
    ADD (UPDATE ON OBJECT::[dbo].[sysschedules] BY [dbo])
    WITH (STATE = ON)
    GO

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.