Post reply

Change database owner

  • March 27, 2020 at 4:48 am

    #3738166

    Hi All

    I would like to change database owner in databases which part of AG group and act as secondary replica

    so when i tried to change the owner , it give me error :

    "Failed to update database because the database is read-only"

    What should I do ?

    Your feedback is much appreciated

    Thank you

  • March 27, 2020 at 7:23 am

    #3738173

    Hi,

    you had to do a failover, to change the database owner. It is not possible to do this on the inactive node.

    Kind regards

    Andreas

  • March 27, 2020 at 5:10 pm

    #3738362

    I found that the easiest way to change the owner of a database in an AG - is the following:

    1. Restore the database to the secondary with RECOVERY (before adding it to the AG)
    2. Change the owner to the expected owner
    3. Restore the database to the secondary with NORECOVERY
    4. Add the database to the AG

    When restoring the database - do not drop the originally restored database, use WITH REPLACE to overwrite the existing database.  This is also useful for other settings that must be set when the database is online - but you don't want to force a failover to the secondary to set those values.

    Of course - this must be done when adding the databases to the AG and if you already have that configured and do not want to remove the database and add it back then the only option would be to failover.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 30, 2020 at 9:59 pm

    #3739109

    Thanks All

Viewing 4 posts - 1 through 3 (of 3 total)

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