Friday, February 27, 2015

Simple Tools Series: Change the owner of all Jobs to the SA login

"Simple Tools Series" - Is a selection of simple every day scripts, that any DBA will need either on a daily basis or frequently.

 Change the owner of all Jobs to the SA login
 Author: Rasmus Glibstrup
 Company: it-Craft A/S, Denmark
 Date: 25-FEB-2015
 Version: 1.0
 Script to change all jobs to be owned by the SA 
 login. This usually prevents errors when AD accounts
 are disabled - eg. consultants, old empl├│yees ect.

 The script works for:
 - SQL Server 2014
 - SQL Server 2012
 - SQL Server 2008 R2
 - SQL Server 2008
 - SQL Server 2005

USE [msdb]
Declare @JobID uniqueidentifier, @JobName sysname
--Cursor to detect and change the jobs not owned by the SA login
Declare JobCursor CURSOR FOR
Select job_id, name 
from sysjobs
where SUSER_SID('sa')<> owner_sid 

OPEN JobCursor
FETCH NEXT from JobCursor into @JobID, @JobName

   --Print the Job name
   PRINT 'JOB: [' + @Jobname + '] - is changed to SA owner'

   --Execute the change, comment the next line if you just want to see the jobs with an non SA login owner
   EXEC msdb.dbo.sp_update_job @job_id=@JobID, @owner_login_name=N'sa' 

   FETCH NEXT from JobCursor into @JobID, @JobName
CLOSE JobCursor