Change SQL Agent Job Owner

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

One of the most common annoyances when DBA team memberships changes, is the fact that any SQL Agent jobs owned by them will now fail to run. Not fail, but fail to run - so there won't even be a fail code reported.

This is firmly supported in it's annoyance level by the fact that there is no GUI or system proc method for changing job ownership. It must be done pro grammatically.

Of course, if only the default job owner was sa and not the logged in user, then this problem would likely not be as prevalent.

Anyway, the code below does the trick.

Test it first though.... :-)

Code

-- 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