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