|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Tuesday, May 15, 2012 4:11 AM
Points: 408,
Visits: 2,661
|
|
Hi DHeath
If you manage 20+ servers, you might want to look into Powershell. I manage 100 of the blighters, and powershell has made my life a lot easier!
Check out this script below, which should do what you want. All you need is a textfile AllServers.txt with your server names in:
SERVERA SERVERA\INST SERVERB ...
The script (in this case I use a SQL authenticated login, but of course you can use Windows authentication as well): EDIT: NB this has only been tested on my local SQL installation!
cls # load assemblies [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$userName = "sqldba" $password = "P@$$w0rd"
# file errors will get sent to $errorfile = "C:\errors_ChangeDBOwner.txt" New-Item -itemType file $errorfile -force | Out-Null
# file output will get sent to $outputfile = "C:\ChangeDBOwner.txt" New-Item -itemType file $outputfile -force | Out-Null
# loop through all SQL Servers $servers = Get-Content "c:\AllServers.txt" try { foreach($srvname in $servers){ $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($srvname) $srv.ConnectionContext.NonPooledConnection = "True" # login using SQL authentication, supplying the username and password $srv.ConnectionContext.LoginSecure=$false; $srv.ConnectionContext.set_Login($userName) $srv.ConnectionContext.Password = $password
$sa = $srv.Logins["sa"] foreach($db in $srv.Databases) { if(!$db.IsSystemObject) { $db.SetOwner($sa.Name) } } $srv.ConnectionContext.Disconnect() } } catch{ $err = $Error[0].Exception while ( $err.InnerException ) { $err = $err.InnerException } $srv.Name + " - " + $db.Name + " : " + "TRAPPED: " + $err.Message | Out-File -append -filePath $errorfile $err }
I use scripts based on this template for all sorts of handy things - checking for failed jobs on all servers, bringing back information on database settings for all servers/databases... It's well worth a look, IMO
------------------------------------------------------------------------ Bite-sized fiction (with added teeth)
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Friday, May 31, 2013 12:23 PM
Points: 203,
Visits: 572
|
|
Hey mazzz,
Thanks a lot and may have to look at the Powershell avenue as well. The script you provided looks to be very useful. Thanks again
DHeath
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, May 27, 2013 1:31 AM
Points: 30,
Visits: 257
|
|
excellent script, just used it to change hundreds of DBs.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:15 AM
Points: 9,
Visits: 88
|
|
I know this topic is a bit old, but I have a different script to share. In this case, it will only change the db owner when a SPECIFIC LOGIN owns the database. It will not change others. It is easy to make it change everything that is "<> 'sa'" too (just change the where condition on the select). It will work on all databases from the current instance.
/* http://thelonelydba.wordpress.com -- list bases owned by specific login. script to change below. SELECT name, SUSER_SNAME(owner_sid) owner FROM sys.databases where SUSER_SNAME(owner_sid) = 'user_to_remove' */ DECLARE @sql nvarchar(4000); DECLARE @BigSQL nvarchar(4000); DECLARE @dbName varchar(100);
declare cbases cursor fast_forward for SELECT name FROM sys.databases where SUSER_SNAME(owner_sid) = 'user_to_remove' open cbases fetch next from cbases into @dbName while @@FETCH_STATUS = 0 begin SET @sql = N'exec sp_changedbowner ''''sa'''''; SET @BigSQL = N'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + ''''; --print @BigSQL EXEC (@BigSQL) fetch next from cbases into @dbName end close cbases deallocate cbases Regards, Mauricio
http://thelonelydba.wordpress.com/
|
|
|
|
|
Mr or Mrs. 500
      
Group: General Forum Members
Last Login: Friday, June 14, 2013 11:08 AM
Points: 508,
Visits: 417
|
|
i execute this against my central management servers:
EXEC sys.sp_MSforeachdb ' IF (SELECT owner_sid FROM sys.databases WHERE name = ''?'') <> 1 ALTER AUTHORIZATION ON DATABASE::? TO [SA] '
|
|
|
|