change owner on sql agent jobs

• torpkev

  SSCarpal Tunnel

  Points: 4819

  More actions

  October 23, 2009 at 12:43 pm

  #136637

  hi all,

  we had an employee leave the company recently, and he had created a number of sql agent jobs across the company, unfortunately, they were all under his login.

  Is there a quick way of query all the agent jobs by the owner? I'd like to go in and change them.. if theres a way to auto-change the owner too that'd be awesome..

  thank you!

  --------------------------

  I long for a job where my databases dont have any pesky users accessing them 🙂

• George M Parker

  SSCertifiable

  Points: 5412

  More actions

  October 23, 2009 at 1:00 pm

  #1070226

  Run the output of this script:

  select 'sp_update_job @job_name = ' + ''''+ name + ''','

  + ' @owner_login_name = ''sa'''

  from msdb..sysjobs

  That should change them all to SA...

• torpkev

  SSCarpal Tunnel

  Points: 4819

  More actions

  October 23, 2009 at 1:04 pm

  #1070228

  ahh msdb has sysjobs.. thanks..

  for whatever reason I was looking in master.. that doesn't make sense at all.. doh.. brain fart i guess 🙂

  thanks

  --------------------------

  I long for a job where my databases dont have any pesky users accessing them 🙂

• torpkev

  SSCarpal Tunnel

  Points: 4819

  More actions

  October 23, 2009 at 1:17 pm

  #1070235

  just in case it'll help someone else out.. i modified your script a little:

  DECLARE @newuser VARCHAR(50)

  SET @newuser = 'DOMAINewuser'

  SELECT 'EXEC msdb..sp_update_job @job_name = ' + '''' + sj.[name] + ''','

  + ' @owner_login_name = ''' + @newuser + ''''

  FROM

  msdb..sysjobs sj INNER JOIN

  master..syslogins sl ON sj.owner_sid = sl.sid

  WHERE sl.[name] IN ('DOMAIN\olduser')

  GO

  It will only work if there is a login for the user already (which in my case, there always was)

  Thanks

  --------------------------

  I long for a job where my databases dont have any pesky users accessing them 🙂