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
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/
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.
