InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 07:21:50 GMT20http://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxi 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] 'Tue, 02 Oct 2012 07:56:18 GMTdwithroderhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxI 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.[code="sql"]/* http://thelonelydba.wordpress.com-- list bases owned by specific login. script to change below.SELECT name, SUSER_SNAME(owner_sid) ownerFROM sys.databaseswhere 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 cbasesfetch next from cbases into @dbNamewhile @@FETCH_STATUS = 0begin 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 @dbNameendclose cbasesdeallocate cbases[/code]Regards, Mauriciohttp://thelonelydba.wordpress.com/Tue, 28 Aug 2012 21:42:54 GMTmauriciorpphttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxexcellent script, just used it to change hundreds of DBs. :-)Tue, 07 Feb 2012 05:58:09 GMTabo_moaazhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxHey 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 againDHeathThu, 10 Feb 2011 13:42:44 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxHi DHeathIf 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:[code]SERVERASERVERA\INSTSERVERB...[/code]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![code="other"]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} [/code]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, IMOThu, 10 Feb 2011 09:52:28 GMTmazzzhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxThis is a nice piece of code that will generate the needed code to make the change as well. Was written by a co-worker for me and if you see the need for it...enjoy.-- code generator to change dbowner to 'sa' for databases not owned by 'sa'declare @dbname sysnamedeclare c_loop cursor forselect name from sysdatabaseswhere sid <> 0x01order by nameopen c_loopfetch next from c_loop into @dbnamewhile @@fetch_status <> -1 begin -- determines loop is continuing if @@fetch_status <> -2 begin -- determines record is still available (not dirty) print 'use ' + @dbname print 'g' + 'o' print 'exec sp_changedbowner ''sa''' print 'g' + 'o' print '' end fetch next from c_loop into @dbnameendclose c_loopdeallocate c_loopDHeathThu, 10 Feb 2011 09:41:35 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspx[font="System"]If you need to change the owner of MASTER,MODEL,TEMPDB, or DISTRIBUTION either there's something horribly wrong with your installation or you are doing something really strange.Tom ThomsonNa tog mi gun tuit mi ach ma thuiteas tog!Thig crìoch air an t-saoghal ach mairidh gaol is ceòl [/font]Thanks a TON!! this is just what i needed Tom, its greatly appreciated and i am sure there will be others looking to use it as well. As for the System databases i have not came across any servers where they are not owned by 'sa' just yet but i honestly would not be surprised IF i have some in that situation. i am guessing i will cross that bridge when i get there. Remember i am coming into this environemt and trying to get things tidy and structured which is a process that will take quite a bit of time and just aiming to do things correctly. Thanks againDHeathThu, 10 Feb 2011 09:02:04 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspx[quote][b]DHeath (2/9/2011)[/b][hr]Ok update.... have tried to use the code snippit of EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa''' and running that on the master database but it errors out with Msg 15109, Level 16, State 1, Line 1Cannot change the owner of the master, model, tempdb or distribution database.Msg 15109, Level 16, State 1, Line 1Cannot change the owner of the master, model, tempdb or distribution database.Msg 15109, Level 16, State 1, Line 1Cannot change the owner of the master, model, tempdb or distribution database.i have tried on SQL 2008 and 2005 with both returning the same error messages...any ideas? Also this also errors out if its ran on a user database with the same errors.DHeath[/quote]basically the set of databases has to be filtered t exclude those four.For SQL 2008 you can use a script something like[code]Use MASTERGODECLARE @sqlBase nvarchar(300) = 'ALTER AUTHORIZATION DATABASE XXX TO SA'+char(13)+char(10)DECLARE @sql nvarchar(max)=''SELECT @sql = @sql+'REPLACE(sqlBase,''XXX'',name) FROM sys.databases' WHERE name NOT IN ('MASTER','MODEL','TEMPDB','DISTRIBUTION')EXEC (@sql)[/code]but your user/login has to have either CREATE DATABASE permission in MASTER or have server level ALTER ANY DATABASE permission in order for this script to have any chance to work if any databases which are currently off-line (maybe also if any databases are currently in standby?). If you need to change the owner of MASTER,MODEL,TEMPDB, or DISTRIBUTION either there's something horribly wrong with your installation or you are doing something really strange.Thu, 10 Feb 2011 08:06:32 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxOk update.... have tried to use the code snippit of EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa''' and running that on the master database but it errors out with Msg 15109, Level 16, State 1, Line 1Cannot change the owner of the master, model, tempdb or distribution database.Msg 15109, Level 16, State 1, Line 1Cannot change the owner of the master, model, tempdb or distribution database.Msg 15109, Level 16, State 1, Line 1Cannot change the owner of the master, model, tempdb or distribution database.i have tried on SQL 2008 and 2005 with both returning the same error messages...any ideas? Also this also errors out if its ran on a user database with the same errors.DHeathWed, 09 Feb 2011 15:29:27 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxThanks...will have to read up on the CMS and give you an update as to what takes place... thats for the input...DHeathWed, 09 Feb 2011 12:57:19 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspx[quote][b]Robert klimes (2/9/2011)[/b][hr]You could try creating a central management server([url]http://msdn.microsoft.com/en-us/library/bb934126.aspx[/url]) an then use [code="sql"]sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'[/code][/quote]As sp_changedbowner is deprecated in SQL 2008, it may be better to use [code]sp_msforeachdb 'ALTER AUTHORISATION DATABASE [?] TO sa' [/code] (if I've got that right - I always used sp_changedbowner, haven't used the new thing yet)Wed, 09 Feb 2011 12:35:16 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxYou could try creating a central management server([url]http://msdn.microsoft.com/en-us/library/bb934126.aspx[/url]) an then use [code="sql"]sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'[/code]Wed, 09 Feb 2011 11:44:55 GMTRobert klimeshttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxThanks for the reply G-Squared =]I am hoping to find something that is on the "free" side as you know how companies dont want to spend any $$$ they dont have too. So was looking to find someone that may have already written one that could be shared. I can find many that changes the table owner but none that changes the database owner. But i do appreciate your response.DHeathWed, 09 Feb 2011 11:36:27 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxI'll recommend Red Gate's Multiscript product for that kind of thing.If you can't get that (check the link at the top of this site), then a dynamic SQL script that queries sys.databases and runs the script you have against each one, is going to be easiest. You'll have to manually run that on each server, but it will get the job done.Multiscript will be easier, and if you're administering multiple servers, it'll pay for itself pretty rapidly.Wed, 09 Feb 2011 11:29:39 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1061409-391-1.aspxHello to all,Thanks for your time and hopefully this wont be too difficult to answer. I need to change 30-40 databases per server (about 20+ servers) and looking for a script that can change the database owner NOT table owner. I have looked around and have not been successful in finding a script that will satisfy this request even within SSC unless i missed it whis is VERY possible. Anyways...I know i can use EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = falsebut that changes one at a time and i am not sure how to incorporate that in a script that will make it a much faster solution. Also i do think that 2005 and 2008 will use the same syntax. Also just for double checking i dont believe that changing the database owner to 'sa' will cause any issues but always like to ask just to reassure before making the change. Thanks againDheath Wed, 09 Feb 2011 11:17:00 GMTDHeath