For SQL Login, the SID is unique to the SQL Server instance level, so SID of SQL Login of Primary and Secondary are different.
It will result in orphan user.
Whereas Windows Login SID are unique to the Domain level. Hence when we create the Windows Login, SID are matching
| Windows Login | SQL Login |
| If you have Windows logins, the login SID in SQL Server will match the SID from Active Directory. So all you need to do is create the same login on both your primary and secondary server. | SQL Server logins are a little more complicated, as the SQL Server assigns the SID when the login is created All database users are linked to logins by SID; if the SID of a login is different on the secondary server, the database users tied to that login will be orphaned. This can be fixed, but it's better to avoid the problem all together |
Solution :
Manually specifying of SID, while creating login on the Secondary SQL Server will resolve the issues.
Execute the sp_help_revlogin on your primary to generate all the create statements for your logins, both Windows and SQL Server. For each Windows Login you get code like this:
CREATE LOGIN [WinDomain\siva] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
This will allow you to just recreate the login on the secondary server.
For each SQL Server Login you will get something like this statement:
CREATE LOGIN [sql_test1] WITH PASSWORD = 0x0100A7B0020EEAB8D0B17D28883AA11267W7575B2F55CEC9EE HASHED,
SID = 0x7DE18E2C10F4FE4CA127E9837FCE4D3C, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Notice that the statement contains the encrypted version of the password and the SID.
This means that when run on the secondary server, the password will match the SID and we will not have any issues after the failover.
