Are the user accounts permission copied to the secondary when failover occurs

Question

Server A and Server B are setup as High Availability Group. Recently, We grand application account permission to do the column level encryption on Primary serer(server A). When database was failed over to Server B. Application got an error "authorization failed". when failed back to server A. Application start working. Are the accounts synchronized during the failover or we have to manually grand same permission to server B? Is it possible to synchronize the accounts to secondary server B while it is in inactive status. The SQL version is 2017.

Answer 1

With SQL Server 2017, you need to manually keep accounts on the different replicas synced. All works best if you are using Windows authentication, but if you need to use SQL Server authentication, make sure that if you recreate a login on a replica, that you use the same security ID (SID) when recreating the login.

In SQL Server 2022, there are options now that didn't exist before, if you have a contained availability group: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16

Answer 2

To add to Greg's answer:

Permissions inside the database are copied to the secondary, since they are stored in the database.

Logins, which are stored in master, are not part of the database and therefore not copied, and this is what Greg is talking about.

Thus, you don't need to re-grant any permissions on they keys. But then again, the error message more suggests a login problem than an issue with access to the encryption keys.