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?

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.

  • Thank you for that, HandyD. I went back and re-looked at the SIDs for the database users and found that those indeed did match on each node, but like you said the SQL logins were different. I'll look over the dbatools command and begin working on the fixes. Thanks again for your help! – vPilot Apr 6 '20 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.