What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user:
exec sp_change_users_login 'report';
go
Copy the SID from the column UserSID
. And if you already have an existing login that you want to preserve the name from on that instance, you can't. There is no way to ALTER LOGIN
and change the SID. So, you will need to drop the login and recreate it:
drop login YourLogin;
go
create login YourLogin
with
password = 'password',
check_policy = off, -- simple password and no check policy for example only
sid = 0xC26909...................;
go
Again, you will want to set the sid
parameter of CREATE LOGIN
to the UserSID
you retrieved from executing sp_change_users_login
with the report
.
Note: You will want to set the password on this/these replica(s) to the same so that you don't have a password mismatch between replicas.
I am assuming that your login(s) in question here are SQL Server logins, as this shouldn't be a problem for Windows Auth domain accounts because the SIDs should be the same on each instance.
You could also leverage contained databases here and have the database principal authenticate at the database level. That way you wouldn't have to worry about orphaned users.
Here is some documentation on this:
BOL reference on Management of Logins and Jobs for the Databases of an Availability Group
KB article on How to transfer logins and passwords between instances of SQL Server
Also, this should go without saying, but test this out in a development/QA environment to verify and ensure proper functionality before hitting production.
I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.
Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.
Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.
Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica
Best Answer
Logins and users work the same way they do with DBM or log shipping. If using SQL Server authentication, you have to sync the SIDs manually (or manually create the login with the right SID) assuming the login is already created on the other instance.
Either way, this is not automatically done. You need to create the login at the instance level to match the DB user. If using Windows authentication, it should have the same SID, but you still need to create the login at the instance level.
Remember, the instance-level login sits outside the AG, so the AG won't do anything there. Not sure why you thought the AG would work this way; it never has, just like DBM and log shipping.
Also, there is no feature called Always On. Please do not refer to AGs that way. http://sqlha.com/2013/04/29/alwayson-is-the-new-activepassive-and-activeactive/