My first project of creating highly available AlwaysOn SQL infrastructure was very challenging. Although I was prepared, and many tasks passed smoothly, I had a big problem after database failover. Database doesn’t work properly, and I had problem with orphaned user.
After researching, found that SQL accounts are not same on both nodes although have a same name. But SID of users aren’t same . . . New challenge started, how to create accounts with same SID on second node. The solution is very simple and took just a few minutes of my time.
On first node need to run query select SUSER_SID (‘Username’), and output will look like 0x4F1A276FD34E2E4BB86BD522B617B9C7.
After that, need to run query on second node:
USE MASTER GO CREATE LOGIN TestUser WITH PASSWORD = '**********', SID = 0x4F1A276FD34E2E4BB86BD522B617B9C7;
After this query, result is created account with identical SID as account on first node.
If you need to “replicate” more than one account, please see post How to transfer logins between SQL servers.