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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | 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 |