ALTER USER statement modifies users on off-air node

Question

I had previously asked a question about Orphaned Users here and was directed to this link in an effort to fix it, but this didn't seem to answer my questions, so I am re-asking after some further review of my system.

We found that several database users on our secondary AG node were orphaned and set as SQL User Without Login, so we were unable to perform a successful failover. The only fix was to run ALTER USER 'user' with login = 'user'; after failover, which mapped the Database login with the SQL login on the 2nd node and allowed the app to connect. Unfortunately, this removed the configuration from the users on the 1st node and set them to SQL User Without Login. We obviously don't want this, because it means manual intervention and additional downtime in an automatic failover.

My questions are:

• Why does an ALTER USER statement on one node affect the other if invoked on that first node?
• Is there a setting within the Availability Group properties that needs to be checked off/added that could be missing in my setup?
• There was a recommendation in another blog that stated to drop the user, and re-create with the SID of the SQL User. How is this different than running an ALTER USER statement, as the SID changes on the DB user to match with the SQL User?

Answer 1

The issue here is that your Login on each node has a different SID on it. When you run ALTER USER it changes the DB user's SID to match the login on that particular node but after a failover the DB User SID no longer matches the SQL Login SID.

The fix is to drop the SQL Login on one node and recreate it using the same SID as the other node. This will ensure the DB user has the same SID as the SQL Logins on both nodes, so after failing over the DB user will map to the login correctly.

You can use dbatools Copy-DbaLogin command to copy the login from Node A to Node B after dropping it from Node B, and this will bring across the SID and password for the login.