08 December,2023 by Tom Collins
Question: I have a SQL Agent job with a Last modified date of : 1/11/23. What activity causes the last modified date to occur ? And also is there a way to find out the change?
I've used this query to identify the jobs and owners
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
Answer: Every time the "Save As " button is clicked the date_modified column changes . This means any aspect of the SQL Agent job changed will cause the Last Modified data to change.
This does make it difficult to Audit . You would need to develop an SERVER AUDIT process to monitor the changes made , store them in a log file . One method to audit and maintain records of SQL Agent Job Changeds is Extended Events
Useful Links to problems solved using Extended Events
How to capture query errors using SQL Server Extended Events
How to Audit user DML with SQL Server Extended Events
How to capture deadlocks using system_health Extended Events
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |