Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Changing db owner script Expand / Collapse
Author
Message
Posted Thursday, February 10, 2011 9:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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)
Post #1062116
Posted Thursday, February 10, 2011 1:42 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:42 PM
Points: 209, Visits: 641
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
Post #1062289
Posted Tuesday, February 7, 2012 5:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 11:33 PM
Points: 31, Visits: 292
excellent script, just used it to change hundreds of DBs.


Post #1248034
Posted Tuesday, August 28, 2012 9:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 25, Visits: 168
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/
Post #1351375
Posted Tuesday, October 2, 2012 7:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:21 AM
Points: 571, Visits: 515
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]
'
Post #1367050
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse