CODEWITHSUNDEEP
sqlsql-serversql-server-agent
Zulu Z
Zulu Z

Reputation: 1121

how to find out who modified SQL Server Agent job?

I need to verify who changed step in job 2 weeks ago, is this possible? I have tried this:

use msdb
go
select j.name, j.date_modified, l.loginname
from sysjobs j
inner join sys.syslogins l on j.owner_sid = l.sid

but is shows only owner of the job not who was actually logged in. thanks

Upvotes: 0

Views: 18743

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

The Answer is NO.You cant find any logs for what happened in the past.But if you want to not be in same situation again.Here is the way to do..

--Create a server Audit:

CREATE SERVER AUDIT [SqlAgentObjectAccess_Audit]
       TO APPLICATION_LOG
      WITH
        (QUEUE_DELAY = 1000
          ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e1f7d882-b26e-4b70-bc03-87af197eb7de'
       )

--Now start the server Audit

ALTER SERVER AUDIT [SqlAgentObjectAccess_Audit] WITH (STATE = ON)

---now you need to turn on audit in MSDB and state which events to be audited

USE [msdb]
go 

    CREATE DATABASE AUDIT SPECIFICATION [SqlAgentObjectAccess_Audit_MSDB]
      FOR SERVER AUDIT [SqlAgentObjectAccess_Audit]
      ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
      ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY   [SQLAgentUserRole]),
      ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
       ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [SQLAgentUserRole])
      WITH (STATE = ON)
GO

Note:
1.You can even login to some share and read those files daily into table and send an email
2.you can audit a list of all the events available HERE

References:
https://blogs.msdn.microsoft.com/sqlagent/2011/02/21/auditing-sql-agent-job-creation-and-deletion/

Upvotes: 3

Jagannath
Jagannath

Reputation: 41

If you are using SQL Server version 2008(Not Express Edition) and above then you can enable AUDIT objects at server level, I think you should be able to find the info you are looking for in the audit log.

Upvotes: 0

Related Questions