SQL Server Agent – Logging Actions and Changes Done to Jobs

jobssql serversql-server-agent

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.

Best Answer

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.

Related Question