Friday, February 25, 2011

How to Change the Owner of All SQL Agent Jobs in a SQL Server Instance

Each job in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed.
If there are a lot of jobs that were created by that owner, this can be a tedious task.
Here we’re opening a cursor and looping through the SQL Agent Jobs in the instance that are owned by the old user (@olduser) and executing the sp_update_job stored procedure to change that to match @newuser.
USE MSDB
GO
declare @jobname varchar (200) declare @oldusername varchar (30) declare @newusername varchar(30) set @oldusername='DOMAIN\oldusername' set @newusername='DOMAIN\newusername' declare cur_jobname cursor LOCAL for select name from sysjobs where suser_sname(sysjobs.owner_sid) =@oldusername open cur_jobname fetch next from cur_jobname into @jobname While @@FETCH_STATUS = 0 begin
EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newusername fetch next from cur_jobname into @jobname end close cur_jobname deallocate cur_jobname

2 comments:

  1. While most people would say that editing the system tables directly is wrong, they don't know why IN THIS CASE it's a bad idea. SQL Server Agent caches job information, editing the table directly won't update the data in the cache. Updating the jobs using sp_update_job as you've written is perfect advice.

    ReplyDelete

Thanks for leaving a comment!