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 USERstatement 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 USERstatement, as the SID changes on the DB user to match with the SQL User?