When someone leaves the organization he leaves behind a lot of friends, colleagues, memories and, if he is a SQL DBA, then one or two SQL Server Agent jobs where he is the owner. 🙂
As a best practice we should create the Job with sa as the owner if we don’t have that we can always change the job ownership. One or two can be done manually but when doing the same for multiple jobs it can be tiring. In that case you can use the below script.
#Script to change the job owner from the one in line 13 to sa
IF OBJECT_ID('tempdb.dbo.#SQLAgentOwner', 'U') IS NOT NULL
DROP TABLE tempdb.dbo.#SQLAgentOwner
CREATE TABLE #SQLAgentOwner (Servername VARCHAR(100), Job_Name VARCHAR(200), Job_Owner VARCHAR(100))
INSERT INTO #SQLAgentOwner
SELECT @@ServerName, sj.name , sp.name
FROM msdb.dbo.sysjobs sj
INNER JOIN sys.server_principals sp
ON sj.owner_sid = sp.sid
where sp.name = 'AccountToChange' -- Change the Account to the one you want to replace with sa
DECLARE @Job_namevar VARCHAR (100)
DECLARE Cursor_ChangeOwner CURSOR
FOR SELECT Job_Name FROM #SQLAgentOwner
OPEN Cursor_ChangeOwner
FETCH NEXT FROM Cursor_ChangeOwner INTO @job_namevar
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb..sp_update_job
@job_name = @Job_namevar,
@owner_login_name = 'sa'
FETCH NEXT FROM Cursor_ChangeOwner INTO @Job_namevar
END;
CLOSE Cursor_ChangeOwner
DEALLOCATE Cursor_ChangeOwner
DROP TABLE #SQLAgentOwner
GitHub link.