Who disabled a job in SQL Server Agent

Question

I have a job in SQL server Agent. Now I see that this job is disabled. Multiple people can log in to server and can modify jobs (department policy).

I want to find out which user disabled this job.

If you cannot ask around and discover who made the change, nor have some kind of ticketing system for changes made to servers, it may suggest you need some *process* changes - limiting the number of people who have such high levels of access would be a good start. — Damien_The_Unbeliever, Jan 29 '19 at 08:27

Alexander Volok · Accepted Answer · 2019-01-29T08:14:39.927

7

Time of the change is the only information you can query by default

SELECT date_modified FROM dbo.sysjobs
WHERE job_id = 'job_id'

In order to capture more information following measures can be setup ahead on sysjobs:

Update trigger
Change Data Capture
SQL Audit
XE Session

Nice example: SQL Server Agent logging actions and changes done to jobs

Another one:

create trigger trigg on msdb.dbo.sysjobs 
after insert , update 
as 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'name_of_mail_profile',
    @recipients = 'aaaa@ad.com',
    @body = 'New job creation or job modification alert',
    @subject = 'Automated Message' ;

edited Jan 29 '19 at 08:14

answered Jan 29 '19 at 08:01

Alexander Volok

5,630
3
17
33

2

Thanks. very useful. name will be more easier than job_id. `SELECT date_modified FROM dbo.sysjobs WHERE name = 'job_name'` – Venkataraman R Oct 06 '20 at 09:54
1

@VenkataramanR, you are right. Job_Id and job_name both are equal identifiers for a some certain job – Alexander Volok Oct 06 '20 at 09:57

Who disabled a job in SQL Server Agent

1 Answers1