How to change database's owner from user account to SA account where databases are in Availability group in the 4 node cluster.

Sai 126 Reputation points
2022-01-21T10:22:55.433+00:00

Need to change database's owner from user account to SA account where databases are in Availability group in the 4 node cluster.

Here my question is

  • Is it a best practice to move the database owner to a 'sa' account or windows service account.
  • SQL Server Availability Group does not update DB owner automatically. How to change DBowner in primary and secondary nodes.

I read the below steps in a article

  1. Apply change to primary replica
  2. Fail over the Availability Group
  3. Apply change to the new primary replica

Is it the only option or any other process to sync automatically?

Please guide me if any risk factors while changing DB ownership.

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
496 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.8K Reputation points MVP
    2022-01-21T23:07:14.517+00:00

    I'm not very good at AGs, but I can't but see that you need to fail over the database through all four nodes to make the change on both. SQL Server needs to update sys.databases.owner_sid in master on all four nodes. On the first change, the sys.database_principals.sid will be updated in the database, and this will be propagated to all secondaries, so you will get a SID mismatch between the database and master, which is a recipe for problems.

    Now, the real reason I post is that I don't think you should make sa the owner. That is not good practice for security reasons. In my opinion, a database should be owned by an SQL login which exists solely to own that database, and which has no permissions whatsoever. It should also be disabled so that it cannot login.

    To do this with your AG, you need to be a little careful. If you just run: 

    CREATE LOGIN MyDB$Owner WITH PASSWORD = 'randomstring'

    the SID will be different on all four instances. So once you have created the login on the first server, pick up the SID from sys.server_principals.sid, and use the WITH SID option to explicitly set the SID when you create the login on the other instances.