Change Owner For All Jobs

Here’s a quick way to change all the Job owners in SQL Server Agent. Lets say you want to set all the owners to the SQL Server Agent Account called MyDomain\SQL_AGT.
You can do this across all jobs by running this simple TSQL Logic. Following the script you can find another set of logic that will check to see which Job owners were changed.

use msdb;
set nocount on
declare @new_job_owner varchar(50)
declare @change_job_owners varchar(max)
set @new_job_owner = 'TP\SQL_AGT' --sql agent service account. set @change_job_owners = ''
select @change_job_owners = @change_job_owners +
'use msdb; ' + char(10) + 'exec sp_update_job @job_name = ''' + name + ''',' + char(10) + '@owner_login_name = ''' + @new_job_owner + ''';' + char(10) + char(10)
from msdb..sysjobs where suser_name(owner_sid) not in ('MyDomain\SQL_AGT') -- change all owners that are not set to new job owner account. order by name asc
exec (@change_job_owners) --for xml path(''), type

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s