February 9, 2011 at 11:17 am
Hello 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 = false
but 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 again
Dheath
DHeath
February 9, 2011 at 11:29 am
I'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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2011 at 11:36 am
Thanks 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.
DHeath
DHeath
February 9, 2011 at 11:44 am
You could try creating a central management server(http://msdn.microsoft.com/en-us/library/bb934126.aspx) an then use sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'
February 9, 2011 at 12:35 pm
Robert klimes (2/9/2011)
You could try creating a central management server(http://msdn.microsoft.com/en-us/library/bb934126.aspx) an then usesp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'
As sp_changedbowner is deprecated in SQL 2008, it may be better to use sp_msforeachdb 'ALTER AUTHORISATION DATABASE [?] TO sa'
(if I've got that right - I always used sp_changedbowner, haven't used the new thing yet)
Tom
February 9, 2011 at 12:57 pm
Thanks...will have to read up on the CMS and give you an update as to what takes place... thats for the input...
DHeath
DHeath
February 9, 2011 at 3:29 pm
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 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot 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
DHeath
February 10, 2011 at 8:06 am
DHeath (2/9/2011)
Ok update.... have tried to use the code snippit ofEXEC 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 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot 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
basically the set of databases has to be filtered t exclude those four.
For SQL 2008 you can use a script something like
Use MASTER
GO
DECLARE @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)
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.
Tom
February 10, 2011 at 9:02 am
[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 Thomson
Na 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 again
DHeath
DHeath
February 10, 2011 at 9:41 am
This 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 sysname
declare c_loop cursor for
select name from sysdatabases
where sid <> 0x01
order by name
open c_loop
fetch next from c_loop into @dbname
while @@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 @dbname
end
close c_loop
deallocate c_loop
DHeath
DHeath
February 10, 2011 at 9:52 am
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
February 10, 2011 at 1:42 pm
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
DHeath
February 7, 2012 at 5:58 am
excellent script, just used it to change hundreds of DBs. 🙂
August 28, 2012 at 9:42 pm
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
October 2, 2012 at 7:56 am
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]
'
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply