Change DB Owner to sa for multiple DB's

  • Riken Patel

    SSC Veteran

    Points: 253

    Comments posted to this topic are about the item Change DB Owner to sa for multiple DB's

  • Ernest Libertucci

    Ten Centuries

    Points: 1394

    use master

    go

    exec sp_msforeachdb 'USE ?;if not exists (select 1 from sys.databases where owner_sid = 0x01 and name = db_name()) exec sp_changedbowner ''sa'''

  • Riken Patel

    SSC Veteran

    Points: 253

    sp_changedbowner is an old procedure which may be removed in future release.

    sp_msforeachdb is undocumented so would rather not use in a production system, particularly as I have heard of a few problems with it, such as missing databases.

  • Ernest Libertucci

    Ten Centuries

    Points: 1394

    Valid points, I was just exploring an alternative. No offense intended!

  • aleksey donskoy

    Mr or Mrs. 500

    Points: 565

    Good Script.

    Two things:

    1. Why do you need to keep the script in the temp table, and not execute it right away?

    2. A databases with a spaces in a name would fail this script. Therefore modyfied the following line as:

    SELECT 'ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO sa'

    Thanks.

    Alex Donskoy

    SQL Server DBA

    Greeneberg Trauriq PA Miami FL

  • Riken Patel

    SSC Veteran

    Points: 253

    Good spot on the spaces issue.

    Not sure how you are thinking of doing it without a temp table, there would be a few ways but they would not be as tidy I don't think.

    Also, with this method, you can substitute the query that inserts into the temp table for a different query and run that exec command, such as dropping multiple tables.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Nice script

    The following is missing AND is_read_only = 0 in select

    INSERT INTO @LoopExecuteScripts

    SELECT 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa'

    FROM sys.databases

    WHERE owner_sid <> 0x01

    AND state_desc = 'ONLINE' --AND is_read_only = 0

    and it will work without errors

    Regards

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply