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

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

Each database in a SQL Server instance has an owner, and you may run into a situation in which that owner needs to be changed. One example of this would be a case when a DBA moves departments, but stays in the organization. In that case, the account would still be active, but you’d probably want to change the database owner.
If there are a lot of databases that were created by that owner, this can be a tedious task.
Here, we’re opening a cursor and looping through the databases in the instance that are owned by the old user (@olduser) and executing the sp_changedbowner stored procedure to change that to match @newuser.
USE MASTER
GO
declare @dbname varchar (50)
declare @oldowner varchar (30)
declare @newowner varchar (30)
declare @sql varchar (300)
set @oldowner='DOMAIN\oldusername'
set @newowner='DOMAIN\newusername'
SET @sql=''
declare cur_dbname cursor LOCAL
for SELECT name
FROM master.sys.databases where SUSER_SNAME(owner_sid)=@oldowner
open cur_dbname
fetch next from cur_dbname
into @dbname
While @@FETCH_STATUS = 0
begin
set @sql='exec ['+@dbname+'].sys.sp_changedbowner ''' + @newowner + ''''
-- PRINT @sql
EXEC (@sql)
fetch next from cur_dbname
into @dbname
end
close cur_dbname
deallocate cur_dbname