Changing SQL Server Agent Jobs Owners

Wrote a quick fix-it today for changing the owner of a SQL Agent Job from one login to another…..

Ever get annoyed by SQL Server Agent Jobs, whether created via a Maintenance Plan or any other method, defaulting the owner to the creator of the job? I know I do. The script below containing dynamic T-SQL will allow you to script out the change scripts needed to modify the jobs without the need of opening and modifying each job manually. Big time saver!!!

Script to change job owner:

1
2
3
4
5
6
7

Select 'EXEC msdb.dbo.sp_update_job 
     @job_name = N''' + replace(name, '''', '''''' ) + ''', 
     owner_login_name = N''new_owner'''
From msdb..sysjobs
Where owner_sid = 
     (Select sid From master..syslogins 
      Where name like '%old_owner%')