How to fix a Replica Read Only DB Owner in an Availability Group

As per our previous article if you set up replica DB’s using the SSMS Wizard you will end up with the wrong DBOwner.

This gets tricky to change as once it is set up the databases on the replica servers are now read only.

There are two ways that I have found to correct this issue.

Method 1 – Failover to secondary method

This is by far the most simple method. Failover the database to the secondary replica and change the DB Owner.

Pros:

  • Simple and quick to do

Cons:

  • Could cause an outage to your application

E.g using T-SQL:

ALTER AUTHORIZATION ON DATABASE::[yourdb] TO [sa];

Or you can do via SSMS by selecting the properties of the database and then going to Files.

Changing DB Owner

Method 2 – Remove and restore to Replica Server(s)

This is a handy process that you can use to fix the replicas after the fact, without impacting the primary database. This method involves removing the database from the replica server and then replacing it.

This time using a method which allows you to set the correct DB owner.

Pros:

  • No outage will be experienced as not touching the primary
  • Cons:

    • Takes a bit of time
    • Requires more technical knowledge

    Below we step through the exact steps required to do this second method:

    1. Connect to the replica instance
    2. Create a backup of the database
      Example:

       
      BACKUP DATABASE [yourdb] TO DISK = N'\\servername\backups\Full_AG_DBNameFix.BAK' 
      WITH NOFORMAT, NOINIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COPY_ONLY, STATS = 10 
      GO 
      
    3. Remove the database from the availability group Note: This will not affect the other replicas
       -- Now remove DB from Replica AG Group (only run this on the secondary you are trying to correct the DB Owner on) 
      ALTER DATABASE [yourdb] SET HADR OFF 
      
    4. Do a restore of the database into full recovery. This is to give you the opportunity to correct the DB Owner name
       --Now (still connected to replica node) restore fully 
      USE [master] RESTORE DATABASE [yourdb] FROM DISK = N'\\servername\backups\Full_AG_DBNameFix.BAK' 
      WITH FILE = 1, NOUNLOAD, STATS = 5 
      
    5. Update the DB owner name. Use sa or whatever you need to use. e.g:
       ALTER AUTHORIZATION ON DATABASE::[yourdb] TO [sa]; 
    6. Restore the database again, this time with norecovery
       --Now (still connected to replica node) restore fully 
      USE [master] RESTORE DATABASE [yourdb] FROM DISK = N'\\servername\backups\Full_AG_DBNameFix.BAK' 
      WITH FILE = 1, REPLACE, NORECOVERY, NOUNLOAD, STATS = 5 
      
    7. Apply a transaction log backup (this needs to be a log backup created after the recent full backup that you created).
       RESTORE LOG [yourdb] FROM DISK = '\\servername\logbackups\DBName_backup_2018_01_16_140023_9917111.trn' 
      WITH NORECOVERY 
    8. Add back to Availability Group
       ALTER DATABASE [yourdb] SET HADR AVAILABILITY GROUP = [yourag] ; 
    9. Check the synchronization status reports to ensure everything is working as expected

    Many environments that I have seen over the years tend to have a number of databases owned by different usernames (mostly the old DBAs AD account).

    The above methods have worked for me many times and the second one is a very low impact method of fixing up DB Owners.

    Rob StGeorge
    Senior SQL Server Database Administrator residing in Auckland, NZ

    Leave a Reply