One method to synchronize SQL users in AlwaysOn groups

I have recently started working with AlwaysOn availability groups. The suggested method to deal with users in AlwaysOn groups is with partially contained databases, then the user is in the DB only, which is replicated to all nodes. However, there are some situations where you cant make a DB a contained DB.

One of the things I like to do with SQL is create a read-only SQL user for reporting name ‘ReportUser’. Not knowing the intricacies of AlwaysOn, I created the SQL user on both nodes using traditional SQL. This worked at first, but when I failed over the node, my access stopped working.

After some googling, I found the answers at https://support.microsoft.com/en-us/kb/918992 and https://msdn.microsoft.com/en-us/library/ms175475.aspx. It turns out that each user has a SID, which is different on each server, but only one matched the SID in the DB.

How I resolved it
I started by looking at the Orphaned User report

exec sp_change_users_login @Action='Report'

This returned the SID of my ReportUser account in the database. I failed the DB over to the other server and then deleted the ReportUser account from my system.
Then, I recreated my user, but this time specifying the SID:
CREATE LOGIN [ReportUser] WITH PASSWORD = ‘oiu234rno89aSdf’, SID = 0x144186B7023F4D43BDA437284FB3E1D9, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Now, since the SIDs match on the two servers, when the DB migrates between the servers, my account works.