SQL SERVER JOBS and USERS

• cqldba

  SSC Enthusiast

  Points: 182

  More actions

  September 28, 2008 at 10:37 am

  #131559

  Hello guys,

  I am looking forward to obtain this information from all our 200 servers

  •Listing of all SQL server job schedule changes since March 01, 2008 -

  •Listing of all job abends/failures since March 01, 2008.

  •Listing of all users with greater than read access to the SQL server job schedules –

  Is there a way to get this list electronically

  I will appreciate if anyone can provide any script or any help to get this quickly from 200 servers.

  or even for one server

  thanks

  sam

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 28, 2008 at 1:06 pm

  #878196

  As for the first, I don't think that's logged.

  For the second, query the sysjobhistory table in msdb. The amount of history depends on the frequency of the job. I think the default is 1000 items per job.

  Not sure about the third, but it will be based on role membership/specific permissions in the msdb database

  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

• john.arnott

  SSChampion

  Points: 11882

  More actions

  September 29, 2008 at 2:51 pm

  #878626

  [p]Jobs and their schedules are in the msdb system database. There's a cool script posted a few months ago at http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/ that has the joins and data formatting to give a report of scheduled jobs based on the three tables sysjobs, sysjobschedules, and sysschedules. Please take a look at my posting in the forum discussion on that posting for a couple of minor corrections to the script.[/p]

  [p]I haven't played with this, but see that both sysjobs and sysschedules have columns named "date_created" and "date_modified". One or more of those may provide the filter you want to limit results to recently changed schedules.

  [/p]

  [p]As for gathering this data from multiple servers, my first inclination would be to set up a fairly simple SSIS package that uses a ForEach loop to spin through a list of servers, doing the schedule query on each one. I like to return data to a SQL table with a runtime column, then copy the desired range of results to Excel.[/p]