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, January 14, 2015 9:53 AM
Points: 209, Visits: 644
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: Thursday, January 22, 2015 11:37 AM
Points: 31, Visits: 295
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: Monday, January 26, 2015 6:15 AM
Points: 30, Visits: 184
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: Friday, January 23, 2015 7:18 AM
Points: 575, Visits: 528
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: Friday, January 16, 2015 10:06 AM
Points: 1, Visits: 13
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse