How do I change owner for SQL Server jobs to SA account

--How do I change owner for SQL Server jobs to SA account

/*

At work, I am in the process of migrating a sql server machine with all the objects to a new SQL server.
As it turns out we have more than 200 jobs that are created by different DBAs and developers!! One of my task
list is to change all the job owners to SA account. How am I going to do that? I’m going to use the stored
procedure sp_update_job to do this. Also I’m going to use a combination of temporary table and while loop,
so let’s go ahead and take a look of this next.

Syntax from MSDN

sp_update_job [ @job_id =] job_id | [@job_name =] 'job_name'
[, [@new_name =] 'new_name' ]
[, [@enabled =] enabled ]
[, [@description =] 'description' ]
[, [@start_step_id =] step_id ]
[, [@category_name =] 'category' ]
[, [@owner_login_name =] 'login' ]
[, [@notify_level_eventlog =] eventlog_level ]
[, [@notify_level_email =] email_level ]
[, [@notify_level_netsend =] netsend_level ]
[, [@notify_level_page =] page_level ]
[, [@notify_email_operator_name =] 'email_name' ]
[, [@notify_netsend_operator_name =] 'netsend_operator' ]
[, [@notify_page_operator_name =] 'page_operator' ]
[, [@delete_level =] delete_level ]
[, [@automatic_post =] automatic_post ]


*/


use master
go

set nocount on


--Declaration of variables


declare

@jobid uniqueidentifier,
@rec_count int,
@loop_counter int


--create temp table to hold the information on job id. We will pull this information from sysjobs table in
--the msdb database

create table
#t_sqljobs
(

id int identity(1,1),
job_id uniqueidentifier,
owner_sid varbinary(100)
)

--insert the data into a temporary table from sysjobs table

insert #t_sqljobs(job_id, owner_sid)
select job_id, owner_sid
from msdb..sysjobs

--Set up the loop counters so we can loop through all the jobs in the temporary table

set @loop_counter=1
select @rec_count=COUNT(*) from #t_sqljobs


--Using a while loop to go through all the records. We first get the job id and then using sp_update_job procedure,
--will change the job owner to SA


while @loop_counter<=@rec_count

begin

select @jobid=job_id from #t_sqljobs where id=@loop_counter
--print 'job id is ' + cast (@jobid as varchar(100))
exec msdb.dbo.sp_update_job @job_id = @jobid, @owner_login_name ='sa'

set @loop_counter=@loop_counter+1

end

--Cleanup and drop the temporary table


drop table #t_sqljobs