T-SQL – Change SQL Job Owners to SA

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

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Tiny URL for this post:
 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield