Case #1: Why are the databases on my Always On secondary replica owned by my account rather than ‘sa’? I am unable to change the database owner on the secondary replicas
Case sections:
| Case Description (go top) |
|---|
| My databases on the primary replica are all owned by sa. I have manually synchronised the databases across all the secondary replicas using the “Join Only” option, by restoring the database and transaction log backups using WITH NORECOVERY option. The databases got added to the availability group successfully. However, sys.databases on the secondary replicas shows that the databases on the secondary replicas are now owned by my DBA account rather than sa. Its against the company policy for any databases to be owned by accounts other than sa. An attempt to change the database owner on the secondary replicas using sp_changedbowner failed with the below error: Msg 976, Level 14, State 1, Line 2 The target database, ‘ProductionDatabaseName’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. An attempt to drop my DBA account from the secondary servers has failed with the below error: Msg 15174, Level 16, State 1, Line 5 Login ‘Domain\DBALogin’ owns one or more database(s). Change the owner of the database(s) before dropping the login. How to change the owner of these databases on the secondary replicas to sa? |
| Resolution (go top) |
|---|
| Simplest way to update the database owner on the secondary replica is to make it the primary replica, by failing over the Availability Group (AG) to it. Once the secondary replica becomes the new primary replica, it will let you update the database owner of the database using sp_changedbowner or using ALTER AUTHORIZATION command – BUT, this method does require a failover to the secondary replica, and multiple failovers if you need to do this on multiple secondary replicas. Each failover would result in a little outage. If any sort of outage is not an option, then unfortunately, you have to remove the databases from the AG, recover the databases on the secondary replicas, change the owner of the databases to sa, re-add and synchronise the databases. You have to find a maintenance window to complete the below steps: 1. Run the below command on the primary replica to remove the databases from the AG: ALTER AVAILABILITY GROUP [AGName] REMOVE DATABASE [DatabaseName] 2. Now you’ll notice that the database on the secondary replica goes into ‘restoring’ state. Run the below command on the secondary replica to recover the database: RESTORE DATABASE [DatabaseName] WITH RECOVERY 3. Change the owner of the database on the secondary replica by running one of the below commands: EXEC DatabaseName..sp_changedbowner ‘sa’ OR ALTER AUTHORIZATION ON DATABASE::DatabaseName TO sa 4. Take a full backup and transaction log backup of the database on the primary replica. Restore over the secondary database using the full and transaction log backups from primary replica, using WITH NORECOVERY option. 5. Repeat steps 2, 3 and 4 on all secondary replicas affected by the issue. 6. On the primary replica, add the database back into the availability group using “Join Only” option, as you’ve already synchronised the secondary databases using baskup/restore: ALTER DATABASE [DatabaseName] SET HADR AVAILABILITY GROUP = [AGName] NOTE: If your databases are small, you could take advantage of the automatic seeding feature to synchronise the databases. If you want to do this, after step 2, drop the database on the seondary replicas. Then, simply add the database back to AG using automatic seeding option. Automatic seeding recreates the secondary database with sa as the owner. Automatic seeding is slow with larger databases though. Check the database owner has been succesfully updated to sa by running the below query: SELECT name, SUSER_SNAME(owner_sid) AS OwnerName FROM sys.databases |
| Root Cause Analysis (RCA) (go top) |
|---|
| In this particular setup, the DBA had created small place holder databases on the secondary replicas. Changed the owner of those placeholder databases to ‘sa’. Then restored the primary database and log backups over the placeholder databases. This is a common practice. Many DBAs first create a placehlder database before restoring over it, which is actually unnecessary and I will show you how this caused the owner name problem here. As I said, the DBA did change the owner of the placeholder database to sa, before restoring over it – but, when restoring over the placeholder database, the restore failed with below error: Msg 3154, Level 16, State 4, Line 2 The backup set holds a backup of a database other than the existing ‘DatabaseName’ database. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. As is common practice, when a DBA sees the above error message, they simply add WITH REPLACE option to the RESTORE command to force the restore – THIS IS THE ROOT CAUSE. In the above error message, SQL Server has complained that the database inside your backup, is different to the placeholder database. The DBA then forced through the restore using WITH REPLACE option. Everytime a database is restored using WITH REPLACE option, SQL Server changes the database owner to the current login running the restore. That means, even if your placeholder database was owned by sa prior to the restore, the WITH REPLACE option changes the database owner to the login running the restore. |
| Prevention (go top) |
|---|
| You have to use one of these method below: Automatic Seeding: In case of smaller databases, just use automatic seeding option. Automatic seeding creates the secondary database with sa as the owner. This is not ideal for larger orbusy databases. Execute in the context of sa: When using ‘join only’ option where you restore the secondary database using the full and transactoin log backups of the primary database with NORECOVERY option, simply run those restores in the context of sa account. You do not need to know the sa password or login as sa to do this. Simply run the below command before the restore command: EXECUTE AS LOGIN = ‘sa’ After the full database and transaction log restore completes, return to your own context by runing the below command: REVERT If you must first create a placeholder database on secondary replica prior to the restore, just usin the above EXECUTE AS technique when restoring using WITH REPLACE option. |
| Additional Notes (go top) |
|---|
| HADR endpoint being owned by non-sa accounts: Sometimes, this problem can manifest in ways other than database ownership. For example, you could end up owning the Always On endpoint (hadr_endpoint) using your own DBA login rather than sa. You can see this by querying the principal_id column of sys.endpoints and sys.tcp_endpoints. As long as a DBA account owns a database or endpoint, SQL Server will prevent that login from being dropped. If that DBA leaves the firm and you want to drop that login, then you’re stuck. You first need to change the ownership of the databases and endpoint. To chang the ownership of an endpoint, run the below command: ALTER AUTHORIZATION ON ENDPOINT::hadr_endpoint TO sa There is no downtime involved with changing the endpoint ownership. Its a best practice to have sa own the databases and endpoints to prevent any permission chaining and orphaned SID issues. Why is the RESTORE command changing database owner when WITH REPLACE option is used? When performing a restore over an existing database, SQL Server checks if the database is being restored by the backup of a different database. By default it prevents a database being restore with the backup of another database. In this case you must use WITH REPLACE option to force the restore. When WITH REPLACE is used, SQL Server changes the target database owner to the current login that is running the restore. This is by design. How does SQL Server know that I am restoring my database, with the backup of another database? SQL Server uses internal GUIDs to uniquely identify each database. The backup files also contain the GUID of the database. When restoring, SQL Server checks if the GUID of the database being restored matches with the GUID of the backup in the backupset. If those GUIDs don’t match, SQL Server raises the error 3154 (mentioned in the RCA section above). Its possible to verify these GUIDs yourself by running the RESTORE HEADERONLY command on the backup, and by querying the DMV sys.database_recovery_status. For example, the BindingID column from RESTORE HEADERONLY column has to match the database_guid column of sys.database_recovery_status DMV. Similarly, RecoveryForkID, FamilyGUID, FirstRecoveryForkID columns from RESTORE HEADERONLY output correspond to family_guid and recovery_fork_guid columns of sys.database_recovery_status DMV. |
