Changing db owner script

  • 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

  • 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

  • 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

  • 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'

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Robert klimes (2/9/2011)


    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'

    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

  • 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

  • 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

  • DHeath (2/9/2011)


    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

    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

  • [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

  • 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

  • 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) [/url]

  • 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

  • excellent script, just used it to change hundreds of DBs. 🙂

  • 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/

  • 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