Change SQL Agent Job Owner

-- assumes current owner will be a domain login

-- but new owner may be a domain login, 'sa', or any valid server login

DECLARE

@OldLoginName SYSNAME

,@NewLoginName SYSNAME

,@new_owner_sid VARBINARY

SELECT

@OldLoginName = N'mydomain\oldLogin'

,@NewLoginName = N'sa'

--list the jobs currently owned by the old owner nane

SELECT SL.name, '|', SJ.[name], SJ.[job_id], SJ.[owner_sid] AS old_owner_sid

FROM msdb..sysjobs SJ

INNER JOIN MASTER..[syslogins] SL ON SJ.[owner_sid] = SL.[sid]

WHERE SJ.enabled = 1

AND SL.[isntname] = 1

AND SL.[name] = @OldLoginName

-- get the SID for the new owner name

SELECT @new_owner_sid = [sid]

FROM MASTER..syslogins

WHERE [name] = @NewLoginName

--update the jobs to be owned by the new owner

;WITH jobs_to_change (old_owner_name, job_id, name, old_owner_sid) AS

(

SELECT

SL.name

,SJ.[job_id]

,SJ.[name]

,SJ.[owner_sid] AS old_owner_sid

FROM msdb..sysjobs SJ

INNER JOIN MASTER..[syslogins] SL

ON SJ.[owner_sid] = SL.[sid]

WHERE SL.[name] = @OldLoginName

)

UPDATE msdb..sysjobs

SET owner_sid = @new_owner_sid

FROM msdb..sysjobs SJ

INNER JOIN jobs_to_change JTC

ON SJ.job_id = JTC.job_id;

-- check that the old owner now does not own any jobs

SELECT

SL.name

,'|'

,SJ.[name]

,SJ.[job_id]

,SJ.[owner_sid] AS old_owner_sid

FROM msdb..sysjobs SJ

INNER JOIN MASTER..[syslogins] SL

ON SJ.[owner_sid] = SL.[sid]

WHERE SJ.enabled = 1

AND SL.[name] = @OldLoginName