Friday, February 25, 2011

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.
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
set @sql='exec ['+@dbname+'].sys.sp_changedbowner ''' + @newowner + ''''
-- PRINT @sql
EXEC (@sql)
fetch next from cur_dbname
into @dbname
close cur_dbname
deallocate cur_dbname

No comments:

Post a Comment

Thanks for leaving a comment!