changing db owner with always on replication (to fix DB properties window)

  • Glen_A

    SSC Eights!

    Points: 988

    SQL Server 2016 standard edition, with primary databases and one other server set up as secondary failover with always on availability group.

    There were some issues over the weekend with an application that accesses the databases on this server, unbeknownst to me, they decided just to reboot the database server, which seemed to clear things up with the application.

    This morning I was checking the databases, as I was going through the emails I was cc'd on it looked like there was permissions issues with tempdb for one of the databases.  One of the databases will not display a properties window, giving the property spaceavailable is not available for database 'x'message.

    I checked the ownership of the databases with a query to make sure it wasn't set to "unkown", and it is owned by a domain service account that owns another database (that I am available to see the properties of).

    Doing some more reading of this (properties) error I see the easiest method to deal with this seems to be just to change the dbowner to sa then change it back. However I also read this can be an issue with failover databases with ownership changes not going over to the failover database.

    Is this an issue I need to be concerned about, if I am changing the owner to sa then back to original owner?

    Is there a better way to fix the unable to see properties window in this particular database?

     

    thanks in advance.

     

     

  • Site Owners

    SSC Guru

    Points: 80373

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Steve Rodgers

    SSC Journeyman

    Points: 84

    Glen_A.   We always set the db owner to 'sa' on all replicated databases.   However, the owner is not replicated from the Primary replica to the Secondaries so you will have to fail over to each replica in turn and set it locally.

  • Glen_A

    SSC Eights!

    Points: 988

    Thanks for the reply, I will take that into consideration and see if I can fit in a failover so I can change it on both nodes.

    I am still mystified however, on why I cannot see the properties of just the one database, while I can see the properties on another that has the same db owner.

     

  • email 73321

    SSC Enthusiast

    Points: 104

    Basically, fail over the AG to each replica and issue

    ALTER AUTHORIZATION ON DATABASE::[AppLiveDb] TO [sa]

    With regards to the properties window, maybe your AG properties are set to not allow reading of the secondary database copies? Personally, I enable "Readable Secondary" (not always read-only routing) on all replicas of the AG just so that I can issue queries against those copies without needing to fail over the AG.

     

  • as1981

    SSCrazy

    Points: 2761

    I don't think Glen_A will be able to enable readable secondary as they are using Standard Edition?

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

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