The Problem
I encountered an issue lately where one particular service account kept losing access on a development instance. Since the login and user were present I went to one of the usual culprits, orphaned users. I ran the following query, and sure enough the account was orphaned.
This is easily fixed using T-SQL:
USE landontestdb
GO
exec sp_change_users_login 'update_one', 'landontest', 'landontest'
Normally this will happen after a database refresh, because the database and its associated logins were created on another instance. That was not the case this time, however. And as I talked with the developer it became clear that this happened fairly frequently. What could cause a user to continually become orphaned in the absence of database refreshes?
The Reason
As it turns out, Availability Group failovers were the culprit (kinda). That shouldn’t be the case, because the database information (including users) is synced to the secondary. However, remember that an AG is comprised of two or more distinct instances and that logins exist at the server\instance level. In this case, the user was matched up correctly to the login on the primary, but when it failed over to the secondary the same login there had a different SID. This can be verified easily:
Primary Instance:
Secondary Instance:
As you can see, the login SID on the secondary instance is not the same as that of the primary. Because of this, every time it failed over to the secondary instance the user and login SIDs would not match, resulting in an orphaned user. This would cause the developer to contact a DBA, who would take the very logical step of fixing the orphaned user. That fix changes the SID of the database user to match the login on the secondary. And then when it failed back to the primary it would be mismatched again, and around and around we go.
Likely this all started when it was first set up, and the DBA created the two logins separately. Instead, it’s better to create it on the primary and then copy it over to the secondary. This can be easily accomplished in PowerShell with dbatools:
Copy-DbaLogin -Source primaryinstance -Destination secondaryinstance -Login landontest
You can also accomplish this more manually by using sp_help_revlogin.
The Takeaway
It can be a bit allusive at first, but this actually turned out to be a very simple fix. And the developer was thrilled to have a more permanent solution that they didn’t have to message the DBAs about every few weeks. Just remember:
- AGs are comprised of multiple instances. Make sure your logins are present on all of them, and that they’ve been copied from the primary rather than created distinctly.
- If you have an orphaned user, you can easily repair it using the method above.
Leave a Reply