Who modified the JOB?

Question

Who modified the JOB?

vignesh.ms

Hall of Fame

Points: 3456
More actions
February 24, 2014 at 3:36 am

#286008

Hi there,
I own a job, that was rescheduled by some one. I want to track from which user the schedule got changed ?
Do I have query for the above???
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Unless you had some custom auditing implemented before that change was made, no way to tell.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

GilaMonster (2/24/2014)
Unless you had some custom auditing implemented before that change was made, no way to tell.

Could you assist me to create custom audit ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

'Custom audit' can be as simple as logging information you want to keep for as long as you want to keep it. Start by figuring out what you want to audit and how long, then create a database to store the information, then create jobs to record that information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Here's an option that can be used. You will need to determine what you want to audit in msdb for the jobs, but this can get you going in the right direction.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

This may not be a route you want to take, but we experienced a similar problem while a server was being used for both DEV/PRODUCTION. I created a trigger on the sysjobs table to email me whenever a job was edited/disabled. It works well, but obviously doesn't track what changes were made. In my case it was "enough" to be able to track down the user in question...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

In the past, you can't tell.

For the future, the usual DML triggers on the msdb job tables can record this information. As always, beware of impersonation (i.e. ORIGINAL_LOGIN() may be useful, plus the usual application name, host name, transaction time, and so on).

Caveat - it gets messier if you're trying to find the user a SID (such as the owner) belongs to for a change two weeks ago, if the user no longer exists. Unless you have a ton of msdb job changes, it's probably not going to hurt performance much to do the joins to track the sids down even in the trigger.