Recently I ran into an issue regarding AlwaysOn availability groups and SQL Logins; Anytime I would failover the AG to a secondary node, I would get an orphaned user. I know that orphaned users are caused by a mismatch of SIDs. So the SID from the Primary database for the SQL User isn't the SID from the secondary database.
I had to put my thinking hat on for this one. I knew that I needed to find a way to match SIDs across the Availability Groups for the failover to work without having to fix the orphaned user. And I discovered that by using T-SQL to create the user, you can specify what SID to use (thanks to http://sqlserverpedia.com/wiki/Moving_SQL_Server_Logins_Between_Servers).
So here are the steps to get SIDs uniform across the board:
1. Create the user on the Primary Node (No need to recreate if already created)
2. Run the following query on the primary node:
SELECT sid FROM sys.server_principals WHERE name = 'SQLUserLogin'
3. Using the SID from the output, create the following query (where SID = the result from step 2):
CREATE LOGIN [SQLUserLogin] WITH PASSWORD=N'Password', SID = 0x0000000000000000000000000;
4. Run the query from step 3 on all secondary nodes of the Availability Group.
5. You now have to go and specify any properties of the Login on the secondary nodes. Examples are password enforcement and user mappings.
6. You should be good to go!
Note: You cannot use Alter Login to change the SID on an already created user. http://msdn.microsoft.com/en-us/library/ms176060.aspx
