Who changed that job? Auditing SQL Agent job changes with SQL Server Audit

#What you get for free

SELECT name, date_created, date_modified, version_number
FROM msdb.dbo.sysjobs ORDER BY date_modified DESC;

version_number goes up with each change. That tells you that something changed and when, but not who or what.

#Who: audit the msdb procedures

Every SSMS change to a job goes through procedures such as sp_update_job, sp_update_jobstep, sp_add_jobstep and sp_delete_job. Audit their execution.

USE master;
CREATE SERVER AUDIT AgentChanges
  TO FILE (FILEPATH = N'D:\SQLAudit\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 20)
  WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT AgentChanges WITH (STATE = ON);

USE msdb;
CREATE DATABASE AUDIT SPECIFICATION AgentJobChanges
FOR SERVER AUDIT AgentChanges
  ADD (EXECUTE ON OBJECT::dbo.sp_add_job        BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_update_job     BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_delete_job     BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_add_jobstep    BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_update_jobstep BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_delete_jobstep BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_add_jobschedule    BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_update_schedule    BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_attach_schedule    BY public),
  ADD (EXECUTE ON OBJECT::dbo.sp_detach_schedule    BY public)
WITH (STATE = ON);

Read it:

SELECT event_time, server_principal_name, client_ip, application_name, object_name, statement
FROM sys.fn_get_audit_file(N'D:\SQLAudit\AgentChanges*.sqlaudit', DEFAULT, DEFAULT)
WHERE object_name LIKE 'sp[_]%job%' OR object_name LIKE 'sp[_]%schedule%'
ORDER BY event_time DESC;

The statement column contains the call with its parameters, for example EXEC msdb.dbo.sp_update_job @job_id=..., @enabled=0.

Tip

Add the same audit spec to your server build. It costs very little because job changes are rare.

#What: keep script snapshots

The audit shows who ran sp_update_jobstep, but to compare old and new commands you need the before state. A nightly job that scripts all jobs to files gives you a diff:

Import-Module dbatools
$out = "D:\JobScripts\$(Get-Date -f yyyy-MM-dd)"
New-Item -ItemType Directory $out -Force | Out-Null
Get-DbaAgentJob -SqlInstance localhost | ForEach-Object {
  $_ | Export-DbaScript -FilePath (Join-Path $out ("{0}.sql" -f ($_.Name -replace '[\\/:*?"<>|]','_'))) -NoPrefix | Out-Null
}

Commit the folder to a local Git repo and git diff shows exactly what changed each day.

#Option: triggers on msdb tables

Some teams put triggers on msdb.dbo.sysjobs and sysjobsteps to log changes into a table.

If you do it, keep triggers tiny, log only, and include them in your post-patching checks.

#Alerting on sensitive changes

For the most important events (a job disabled, a step command changed on a backup job), see the job enable/disable alert post. That alert runs on the audit or snapshot data and emails the team.

#Prevent untracked changes

• Few people with rights to change production jobs (msdb roles, not sysadmin for everyone).
• Changes deployed from scripts in source control.
• Audit as a safety net, not the main control.