Change job owner for Agent jobs

create procedure dbo.sp_change_job_owner
@jobName varchar(500),
@ownername varchar(60)
AS

DECLARE @owner_sid varbinary(85)

Select @owner_sid= sid
FROM master.sys.syslogins(NOLOCK)
WHERE name like @ownername

UPDATE msdb.dbo.sysjobs set owner_sid = @owner_sid
where name like @jobName

UPDATE msdb.dbo.sysschedules SET msdb.dbo.sysschedules.owner_sid= @owner_sid
FROM msdb.dbo.sysjobs job
inner join msdb.dbo.sysjobschedules jobsch on job.job_id = jobsch.job_id
INNER JOIN msdb.dbo.sysschedules sch on sch.schedule_id = jobsch.schedule_id
WHERE job.name like @jobName

Leave a Reply

Your email address will not be published. Required fields are marked *