How to Synchronize logins/users between Mirrored Servers

Now that you have mirrored your servers, it does not mean that your users are also mirrored (isn’t it nice to have this feature included in next SQL Server releases).  You should configure your users properly as well so when a disaster hits you wont have a login error issue when your application user tries to connect to the failover partner and that server cannot recognize that user.

Now you might be wondering why you did added the same user name in both mirrored servers but got a login failed exception. While you think you created exactly the same login they are not 100% the same as they have different SID’s (Security Identifier). To verify that run this TSQL in both servers that are mirrored where you had created your user.

SELECT SID, Name from syslogins

As you can see we created a user called “SampleLogin” in both serves but different SID’s were given, this is what gives you a similar error like this.

Cannot open database “Database Name” requested by the login. The login failed. Login failed for user ‘SampleLogin’.

Here is a screenshot.

Now to prevent that from happening the user should have the same SID and you have to indicate that on creation of the user. So here are the steps, first create your login by issuing this command on your first server, take note that the user is enclosed with brackets

CREATE LOGIN [YourUser] WITH PASSWORD = 'YourComplicatedPassword'

Once you have successfully created the user run this command to get the SID

SELECT SID from syslogins WHERE Name = 'YourUser'

Now into your second server, create a user with the same info but with the SID from the query result you got from the first server (the query above)

CREATE LOGIN [YourUser] WITH PASSWORD = 'YourComplicatedPassword', SID = {your SID here}

Now you have the exact user and all you need to do is assign the proper roles and rights on both servers, assign only on the active one as you can’t assign on the one mirroring, once done on the active, failover your database and assign same roles and rights on the other server.  Then you are all good to go.

UPDATE : You still have to run

EXEC sp_change_users_login 'Auto_Fix', 'YourUser'

To fix the user