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

szh8866-8371 80 Reputation points
2024-02-13T02:17:03.86+00:00

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.

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,323 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Greg Low 600 Reputation points Microsoft Regional Director
    2024-02-13T02:26:23.1533333+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 99,461 Reputation points MVP
    2024-02-13T22:33:15.7+00:00

    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.

    1 person found this answer helpful.