SQL Server Performance Forum – Threads Archive

Triggers to monitor jobs on all servers

Hi again – 2 questions in quick succession. I want to be able to monitor if anyone modifies Jobs. I have created a quick trigger to prove it’s possible with a query showing the old and new lines in the jobs table in the msdb database. Just wondering if anyone has a script written they could point me in the direction of? I would rather the email sent just showed the singular modifcations made to the jobs, the user who made the change, time etc. Basically, there a bunch of people who have been complaining that their jobs have been stopped or disabled by other people – I’m sure this isn’t true and it’s just user error but I need the ability to track changes to prove this to them. Dave.

Hi, for the purpose "Monitor any alteration done…., " the record that deleted/updated is stored in deleted table and new record is stored in "inserted" magic tables from this two tables you can get the old and new records with time stamp,username etc. hsGoswami

Hah! Yeah thanks. I was actually wondering if anyone had already seen a script to format all the changes nicely – the main problem is that if someone edits a job then about 20 triggers are fired as they modify each item. I would rather send 1 email for each job that has changed. I think I am going to have a copy of the jobs table which i can compare inside a scheduled job each day or every hour and send out an email summary of any mods made. No idea how to find out who made the changes though. Any ideas? Dave. Small Businesses
www.matiogi.com

]]>