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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 4:11 AM
Points: 369, 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, May 13, 2015 10:33 AM
Points: 209, Visits: 647
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: Monday, May 4, 2015 4:15 AM
Points: 31, Visits: 305
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: Tuesday, May 5, 2015 12:15 PM
Points: 31, Visits: 196
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: Tuesday, May 19, 2015 2:38 PM
Points: 575, Visits: 537
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
Posted Wednesday, December 31, 2014 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 1, Visits: 38
This is the proper syntax for the sp_MSforeachdb. I've tested this successfully in SQL 2008 R2, but it should be good for 2005 on up.


EXEC sp_MSforeachdb
'
EXEC [?].[dbo].sp_changedbowner @loginame = N''sa'', @map = ''false''
'

You can also add or remove selectively. The below shows NOT IN and will change all databases except those listed, but you can of course use IN instead if you only want to change a specific few that you know the names of:

EXEC sp_MSforeachdb
'
IF ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')
EXEC [?].[dbo].sp_changedbowner @loginame = N''sa'', @map = ''false''
'
Post #1647567
Posted Thursday, May 7, 2015 4:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 301, Visits: 860
You could just add some string concatenation to build the statements you need to run off of this:

select name, suser_sname(owner_sid) as owner, [Put your string concatenation here] AS [Statement to Run]
FROM sys.databases
where suser_sname(owner_sid) <> 'sa'
and database_id > 4

You could even run this from registered servers and get it all dumped in one go.

HTH,
Jon
Post #1683748
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse