TSQL to change SQLAgent job owner

Today I discovered many SQLAgent jobs had non-sa ownership of the job. It was replication jobs created by me.

Here is a TSQL script to change job ownership from whatever user to ‘sa’. Or to whatever user you want.

Just change two parameters: @NewOwner and @OldOwner.

DECLARE @job_id2 UNIQUEIDENTIFIER
	,@NewOwner VARCHAR(200)
	,@OldOwner VARCHAR(200)

SET @NewOwner = 'sa'
SET @OldOwner = 'insert_user_name_to_be_changed'
SET @OldOwner = '%' + @OldOwner + '%'  

IF object_Id('tempdb..#JobsToBeRenamed') IS NOT NULL
	DROP TABLE #JobsToBeRenamed  

SELECT a.NAME AS jobName
	,a.job_id
	,b.NAME AS UserLogin
	,ISNULL(suser_sname(a.owner_sid), N'''') AS [OwnerLoginName]
INTO #JobsToBeRenamed
FROM msdb.dbo.sysjobs a
LEFT OUTER JOIN master.sys.syslogins b ON a.owner_sid = b.sid
WHERE ISNULL(suser_sname(a.owner_sid), N'''') != 'sa'
	AND ISNULL(suser_sname(a.owner_sid), N'''') LIKE @OldOwner  

SELECT *
FROM #JobsToBeRenamed  

WHILE (
		SELECT COUNT(*)
		FROM #JobsToBeRenamed
		) > 0
BEGIN
	SELECT TOP 1 @job_id2 = job_id
	FROM #JobsToBeRenamed  

	EXEC msdb.dbo.sp_update_job @job_id = @job_id2
		,@owner_login_name = 'sa'  

	DELETE
	FROM #JobsToBeRenamed
	WHERE job_id = @job_id2
END

SET @NewOwner = ‘sa’
SET @OldOwner = ‘insert_user_name_to_be_changed’
SET @OldOwner = ‘ % ’ + @OldOwner + ‘ % ’

IF object_Id(‘tempdb..#JobsToBeRenamed’) IS NOT NULL
	DROP TABLE #JobsToBeRenamed

SELECT a.NAME AS jobName
	,a.job_id
	,b.NAME AS UserLogin
	,ISNULL(suser_sname(a.owner_sid), N””) AS [OwnerLoginName]
INTO #JobsToBeRenamed
FROM msdb.dbo.sysjobs a
LEFT OUTER JOIN master.sys.syslogins b ON a.owner_sid = b.sid
WHERE ISNULL(suser_sname(a.owner_sid), N””) != ‘sa’
	AND ISNULL(suser_sname(a.owner_sid), N””) LIKE @OldOwner

SELECT *
FROM #JobsToBeRenamed

WHILE (
		SELECT COUNT(*)
		FROM #JobsToBeRenamed
		) > 0
BEGIN
	SELECT TOP 1 @job_id2 = job_id
	FROM #JobsToBeRenamed

	EXEC msdb.dbo.sp_update_job @job_id = @job_id2
		,@owner_login_name = ‘sa’

	DELETE
	FROM #JobsToBeRenamed
	WHERE job_id = @job_id2
END