SQL Server Jobs

Beano

Does anybody know if SQL Server keeps a log of changes to SQL Agent jobs? I dont mean the history of when the job has ran i mean a history of when a user went into the job properties and changed something.

nialo

Not as far as i know. mite be wrong thou. Think the only way of getting this type of info would be to create a trigger on modifications to the sysjobs table and logging it to a custom table.

Beano

nialo wrote: »

Not as far as i know. mite be wrong thou. Think the only way of getting this type of info would be to create a trigger on modifications to the sysjobs table and logging it to a custom table.

well the change has already been made, we're looking for the culprit. thanks anyway.

amen

is you are all logging in using unique logings you look and see who logged in for what reason?

also have a change log that should updated with the reason why something changed

maybe also tell the team its not to try and catch someone but more that you know a change has occured esp if something goes wrong

kayos

You can get the modified time alright but the user sadly I dont think so. Maybe digging around the sys tables in msdb might help...

This just highlights the fact access to these things needs to be restricted to those you can trust to know what they are doing.

Ginger

Did the person do it remotely or do you have to login to the server and do it. POssibly checking the logins on the Security events might give you an idea.

Otherwise

Possibly

IN SysJobs there is a SID of the owner and you can use this tool to get the Username http://www.eytcheson.com/help35/html/hs60200.htm

Cant verify if this will give you your culprit

kayos

Ginger wrote: »

Possibly

IN SysJobs there is a SID of the owner and you can use this tool to get the Username http://www.eytcheson.com/help35/html/hs60200.htm

Cant verify if this will give you your culprit

Sadly it wont, you can assign the owner when creating the job i.e. log in as say domain\kayos and assign the owner to say the sa account. This does not change when you make an edit.

Sadly there is nothing in the SQL Server logs either when you make a Agent change. I'm guessing if its not there and not in msdb..sysjobs you wont get it handy unless the person actually logged into the server and you use the modified time on the job to trace back who was on the server when the change was made.

Ginger

It was a very long shot.. did a quick snoop around the sys* tables and nothing jumped out at me other than that SID as a possible..

Ginger

Only way to notify you in the future I would guess would be to put DML triggers on the tables and add an email notification..

Beano

Thanks for all the advice guys. We're going to drop it. The change was done on a customer site by one of the customers own it guys. We can prove it wasnt one of us so we're leaving them to conduct their own witchhunt.