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