Transfer Login to Replica servers
Recently I was working on configuring AlwaysOn replica setup. After a fail-over my application was not able to connect to the SQL server using the same SQL accounts. The error was “Login failed for user”
The reason is SQL Server never replicates the SQL Server Login, though it replicates the database users. So on the replica you may see the database users, however these accounts are not associated with a database login and this these accounts will remain unusable.
Creating the login manually on the replica is not going to help as the SID of these login will not match.
How I can make this work?
There are two ways you can do this. The first was is to simply enable the partial containment for the database. As MSDN suggests, contained databases can perform the user authentication which reduces the databases dependency on the logins of SQL Server instance.
To enable partial containment you should first enable the containment feature at the server level
EXEC sp_configure 'show advanced', 1; RECONFIGURE; EXEC sp_configure 'contained database authentication', 1; RECONFIGURE;
Once you active the contained database authentication you can enable the partial containment for the database
ALTER DATABASE DemoDB SET CONTAINMENT = PARTIAL;
Hereafter, the logins credential will be part of your database and will not really depend on the server settings.
However enabling the partial containment is associated with few security risks which you should be aware of. You can read more about risks of enabling the partial containment.
If you are not really ready to enable the partial containment, other other option is to use the procedure sp_help_revlogin. You can get the code for sp_help_revlogin here. This stored procedure scripts out the SQL Server logins along with the SID.
Notice that the SID, password and other options are also included in the script.
You can run the script generated on your secondary replicas to fix the login issues. You also could automate this task to replicate the logins on secondary jobs using a simple SSIS Packages
References
http://support.microsoft.com/kb/918992
Attachment: sp_help_revlogin
