SQL Server Agent logging actions and changes done to jobs

All we need is an easy explanation of the problem, so here it is.

Is there a way to track changes done to SQL Server Agent jobs?

If you had a reason to believe somebody is playing around with your job settings. How would you proceed to track who did it and from where the changes were made to the individual job?

I’m thinking along the lines of extended events.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

For sake of simplicity, I will assume that you want to track sysjobs, sysjobsteps and sysjobschedules. There may be other tables you want to monitor.

Option 1: SQL Audit (requires Enterprise Edition)

USE [master]
GO

-- Audit
CREATE SERVER AUDIT [jobs]
TO FILE 
(   FILEPATH = N'PathToSomeFolder'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e807469a-6c9d-43f1-af46-cf7b89ba898d'
)
ALTER SERVER AUDIT [jobs] WITH (STATE = ON)
GO

USE [msdb]
GO

CREATE DATABASE AUDIT SPECIFICATION [job_changes]
FOR SERVER AUDIT [jobs]
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobsteps] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobschedules] BY [public])
WITH (STATE = ON)
GO

The data captured by the audit appears like this:
SQL Audit

Option 2: Extended Events session

-- Step 1: extract object_id for the following tables
SELECT object_id 
from sys.tables
WHERE name IN ('sysjobs','sysjobsteps','sysjobschedules');

-- Step 2: use those object_ids in the following session:
CREATE EVENT SESSION [capture_job_changes] ON SERVER
ADD EVENT sqlserver.lock_acquired (
    SET collect_database_name = (0)
        ,collect_resource_description = (1)
    ACTION(sqlserver.client_app_name, sqlserver.is_system, sqlserver.server_principal_name)
    WHERE (
        [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[equal_uint64]([database_id], (4))  -- msdb
        AND (
               [object_id] = 1125579048 -- sysjobs
            OR [object_id] = 1269579561 -- sysjobsteps
            OR [object_id] = 1477580302 -- sysjobschedules
        )
        AND (
               [mode] = (8) -- IX
            OR [mode] = (5) -- X
        )
    )
)
WITH (
     MAX_MEMORY = 20480 KB
    ,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = OFF
);
GO

-- Step 3: add a convenient target to the session (file target?)

The data in the XE session appears like this:
XE session

Regarding this second option, I wrote a blog post on a similar subject (tracking object usage) where I describe the details of the technique. Basically, you can consider IX/X locks as updates to the underlying tables.

This session captures the bare minimum, but you can add more fields/actions to it to capture the sql text or the computer name or whatever makes sense for you.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply