Agent Jobs

Change SQL Job Owner Based On Former Owner Name

The following logic checks for a particular user names that are possible SQL Job owners. Then returns a list to you of the found Jobs with said owners. Then it changes those Job owners to ‘sa’, and runs the list again. If no jobs are returned then the Job owners were changed accordingly. It’s always a good idea to run the query again using the new ‘sa’ to ensure all Jobs return the proper ‘sa’ as the owner name.

use master;
set nocount on

-- create temp table to hold job info
if object_id('tempdb..#job_owners') is not null
drop table #job_owners
create table #job_owners
(
server_name varchar(255)
, job_owner_name varchar(255)
, job_name varchar(255)
, date_created datetime
, is_enabled varchar(255)
)
insert into #job_owners
select
'server_name' = @@servername
, 'job_owner_name' = upper(suser_sname(owner_sid))
, 'job_name' = upper(sj.name)
, 'date_created' = date_created
, 'is_enabled' =
case enabled
when '0' then 'NOPE'
when '1' then 'YES'
end
from
msdb..sysjobs sj

-- query indo directly from temp table
select
server_name
, job_owner_name
, job_name
, 'date_created' = left(date_created, 12) + ' ' + datename(dw, date_created)
, is_enabled
from
#job_owners
where
job_owner_name in ('MyDomain\MyUserName1', 'MyDomain\MyUserName2') -- <-- find jobs with these owners
order by
date_created
, job_name asc

-- change database owner names for the above found jobs with said owners. change job owners to 'sa'
use msdb;
set nocount on
declare @change_job_owners varchar(max)
set @change_job_owners = ''
select @change_job_owners = @change_job_owners +
'exec msdb..sp_update_job @Job_name = ''' + job_name + ''', @owner_login_name = ''sa'';' + char(10)
from #job_owners
exec (@change_job_owners)

-- run the query again to confirm jobs have been changed, and nothing is returned.

if object_id('tempdb..#job_owners') is not null
drop table #job_owners
create table #job_owners
(
server_name varchar(255)
, job_owner_name varchar(255)
, job_name varchar(255)
, date_created datetime
, is_enabled varchar(255)
)
insert into #job_owners
select
'server_name' = @@servername
, 'job_owner_name' = upper(suser_sname(owner_sid))
, 'job_name' = upper(sj.name)
, 'date_created' = date_created
, 'is_enabled' =
case enabled
when '0' then 'NOPE'
when '1' then 'YES'
end
from
msdb..sysjobs sj

select
server_name
, job_owner_name
, job_name
, 'date_created' = left(date_created, 12) + ' ' + datename(dw, date_created)
, is_enabled
from
#job_owners
where
job_owner_name in ('MyDomain\MyUserName1', 'MyDomain\MyUserName2') -- <-- find jobs with these owners
order by
date_created
, job_name asc

drop table #job_owners

[end]

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s