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 for SQL Server 2005..
Here is a script I wrote to do the heavy lifting:
USE [msdb]
GO
— Surpress the rows affected message
SET NOCOUNT ON
GO
/*
Script to Change SQL JOB owners to sa.
Some jobs may want to have alternate
owner for example a domain login or
local server login.
By Default, whenever a sysadmin changes a job
on the server the ownership changes to
that admin.
The following script changes those jobs to
sa ownership. It’s easy to convert this to a
stored procedure.
— JCD 12-24-2008
MERRY CHRISTMAS!!! or
Enjoy Whatever Holiday Your Faith Recognizes.
I’m a Christian so CHRISTMAS ROCKS 4 ME!
*/
— Create Variables to use to setp through temp table
DECLARE @i INT
SET @i = 1
— Check for an Drop Temp table #SYSJOB_IDS
IF OBJECT_ID(‘tempdb..#SYSJOB_IDS’,’u’)IS NOT NULL
BEGIN
DROP TABLE #SYSJOB_IDS
END
BEGIN
CREATE TABLE #SYSJOB_IDS (
[id] INT IDENTITY(1,1) NOT NULL
,[job_ID] UNIQUEIDENTIFIER NOT NULL
,[name] SYSNAME NOT NULL
,[CurrOwner] SYSNAME NOT NULL
)
END
/*
Populate Temp Table With Job_ID’s that
do not have an owner of sa.
*/
INSERT INTO #SYSJOB_IDS(job_ID, [name],[CurrOwner])
SELECT DISTINCT
a.[job_id]
,a.[name]
,b.[name][CurrOwner]
FROM msdb..SYSJOBS as a
JOIN master.sys.syslogins as b
ON b.[sid] = a.[owner_sid]
WHERE b.[name] <> ‘sa’
ORDER BY a.[name]
/*
– Loop Thru of Temp Table #SYSJOB_IDS
to populate @job_id
– EXEC msdb.dbo.sp_update_job command
to change JOB OWNER (OWNER_SID) to ‘SA’
*/
WHILE @i < = (SELECT MAX(id) from #SYSJOB_IDS)
BEGIN
DECLARE @job_id NVARCHAR(36)– Used to pass job_id
,@name sysname
,@CurrOwner NVARCHAR(40)
— Populate From temp Table
SELECT TOP 1
@job_id = [job_ID]
,@name = RTRIM([name])
,@CurrOwner = RTRIM([CurrOwner])
FROM #SYSJOB_IDS
WHERE id = @i
Declare @Run INT
EXECUTE @Run = [msdb].[dbo].[sp_update_job] @job_id=@job_id , @owner_login_name=N’sa’
–Litte Error Checking and Display Message upon execution
IF @Run = 0
BEGIN
— Display Message that Job Has been updated
SELECT UPPER(@name) + ‘ owner was changed from ‘+ UPPER(@CurrOwner)+’ to sa.’
END
ELSE IF @Run <> 0
BEGIN
— Display Message that Job Has been updated
SELECT ‘Fail: ‘+ UPPER(@name) + ‘ owner was not changed from ‘+ UPPER(@CurrOwner)+’.’
END
–Increment @i to go to Next Value
SELECT @i = @i+1
END
GO
Tiny URL for this post:
Comments are closed.