CODEWITHSUNDEEP
sqlsql-serverssissql-server-agent
user2352554
user2352554

Reputation: 531

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.

Upvotes: 3

Views: 12623

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

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 = '[email protected]',
    @body = 'New job creation or job modification alert',
    @subject = 'Automated Message' ;

Upvotes: 7

Related Questions