I’m a sucker for the Maint Wizards and everytime I touch a SQL Job.. It reverts the ownership to my domain account. ( Not a Good Practice to use my domain account … I know). I manually change the ownership back to sa (actually, dbo as sa is disabled…ssssh) . OR I CAN USE THE BELOW HANDY-DANDY SCRIPT . As always .. Buyer-Beware! Written\u00a0for \u00a0SQL Server 2005..<\/p>\n
Here is a script I wrote to do the heavy lifting:<\/p>\n
<\/p>\n
USE [msdb]
\nGO
\n— Surpress the rows affected message
\nSET NOCOUNT ON
\nGO
\n\/*
\nScript to Change SQL JOB owners to sa.
\nSome jobs may want to have alternate
\nowner for example a domain login or
\nlocal server login.<\/p>\n
By Default, whenever a sysadmin changes a job
\non the server the ownership changes to
\nthat admin.<\/p>\n
The following script changes those jobs to
\nsa ownership. It’s easy to convert this to a
\nstored procedure.<\/p>\n
— JCD 12-24-2008<\/p>\n
MERRY CHRISTMAS!!!\u00a0 or
\nEnjoy Whatever Holiday Your Faith Recognizes.<\/p>\n
I’m a Christian so CHRISTMAS ROCKS 4 ME!<\/p>\n
*\/
\n— Create Variables to use to setp through temp table
\nDECLARE @i INT
\nSET @i = 1<\/p>\n
— Check for an Drop Temp table #SYSJOB_IDS
\nIF OBJECT_ID(‘tempdb..#SYSJOB_IDS’,’u’)IS NOT NULL
\nBEGIN
\nDROP TABLE #SYSJOB_IDS
\nEND
\nBEGIN
\nCREATE TABLE #SYSJOB_IDS (
\n[id] INT IDENTITY(1,1) NOT NULL
\n,[job_ID] UNIQUEIDENTIFIER NOT NULL
\n,[name] SYSNAME NOT NULL
\n,[CurrOwner] SYSNAME NOT NULL
\n)
\nEND<\/p>\n
\/*
\nPopulate Temp Table With Job_ID’s that
\ndo not have an owner of sa.
\n*\/<\/p>\n
INSERT INTO #SYSJOB_IDS(job_ID, [name],[CurrOwner])
\nSELECT DISTINCT
\na.[job_id]
\n,a.[name]
\n,b.[name][CurrOwner]
\nFROM msdb..SYSJOBS as a
\nJOIN master.sys.syslogins as b
\nON b.[sid] = a.[owner_sid]
\nWHERE b.[name] <> ‘sa’
\nORDER BY a.[name]<\/p>\n
\/*
\n– Loop Thru of Temp Table #SYSJOB_IDS
\nto populate @job_id
\n– EXEC msdb.dbo.sp_update_job command
\nto change JOB OWNER (OWNER_SID) to ‘SA’
\n*\/
\nWHILE @i < = (SELECT MAX(id) from #SYSJOB_IDS)
\nBEGIN
\nDECLARE @job_id NVARCHAR(36)– Used to pass job_id
\n,@name sysname
\n,@CurrOwner NVARCHAR(40)
\n— Populate From temp Table
\nSELECT TOP 1
\n@job_id = [job_ID]
\n,@name = RTRIM([name])
\n,@CurrOwner = RTRIM([CurrOwner])
\nFROM #SYSJOB_IDS
\nWHERE id = @i<\/p>\n
Declare @Run INT<\/p>\n
EXECUTE @Run = [msdb].[dbo].[sp_update_job] @job_id=@job_id , @owner_login_name=N’sa’
\n–Litte Error Checking and Display Message upon execution
\nIF @Run = 0
\nBEGIN
\n— Display Message that Job Has been updated
\nSELECT UPPER(@name) + ‘ owner was changed from ‘+ UPPER(@CurrOwner)+’ to sa.’
\nEND
\nELSE IF @Run <> 0
\nBEGIN
\n— Display Message that Job Has been updated
\nSELECT ‘Fail: ‘+ UPPER(@name) + ‘ owner was not changed from ‘+ UPPER(@CurrOwner)+’.’
\nEND<\/p>\n
–Increment @i to go to Next Value
\nSELECT @i = @i+1
\nEND<\/p>\n
GO<\/p>\n","protected":false},"excerpt":{"rendered":"
I’m a sucker for the Maint Wizards and everytime I touch a SQL Job.. It reverts the ownership to my domain account. ( Not a Good Practice to use my domain account … I know). I manually change the ownership …<\/p>\n