Search code examples
sqlsql-serverssissql-server-agent

Who disabled a job in SQL Server Agent

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.

Solution

  • 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' ;