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.
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
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?)
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 🙂