Script to Change the db owner of all databases
I was in need of a script to change the owner of all the database in a server. I checked in the internet but couldnt find any thing so I made this by myself :)
DECLARE curDBNames CURSOR READ_ONLY FOR SELECT name FROM sys.databases where
name NOT IN('master', 'model', 'msdb', 'tempdb','resource') and state = 0
DECLARE @dbName as varchar(1000)
DECLARE @Sql as varchar(1000)
declare @dbOwnerName as varchar(100)
set @dbOwnerName = 'sa'
OPEN curDBNames
--PRINT @@ROWCOUNT
FETCH NEXT FROM curDBNames INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
set @Sql = 'use [' + @dbName + ']'
exec (@Sql)
set @Sql = 'sp_changedbowner ' + @dbOwnerName
exec (@Sql)
FETCH NEXT FROM curDBNames INTO @dbName
END
CLOSE curDBNames
DEALLOCATE curDBNamesHope you like the script. Let me know if something doesnt work. As usual pls double check before you try this in your production environment.
0 Secrets:
Post a Comment