Thursday, August 11, 2011

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 curDBNames
Hope 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: