Login failures connecting to new principal after SQL Server Database Mirroring failover

By:   |   Comments (13)   |   Related: > Database Mirroring


I have configured Database Mirroring successfully between 2 SQL Server 2005 instances. My application is connecting to SQL Server using a SQL Server login, and is built using ADO and the SQL Native Client. My connection strings/connection settings specify the correct information, including the appropriate failover partner. I have also created all the same logins on the mirrored server as on the principal server. Upon testing a database failure, the mirror successfully assumes the principal role and everything looks correct on the SQL Server. I can even connect successfully to the mirror using my Windows login. However, the application reconnect fails with the following error:

Cannot open database "<db name>" requested by the login. The login failed.

It appears as if the login is not associated with a user on the new principal (originally the mirror) database. I run sp_change_users_login to synchronize the users and logins for the database, and I get a message saying it fixed multiple orphaned users. My application then reconnects successfully to the new principal server. I have tried multiple failovers, and each time I see the same behavior, i.e. the association between the login and user gets lost. Is there a way to configure the mirroring setup so this is not a problem?


Yes - This problem occurs because the SIDs (security identifiers) for the SQL Server logins on each server do not match. Although the names for the logins are the same, the login is resolved via the SID. This is not a problem with Windows/Domain user/group logins because the SIDs for these logins are created based on the domain SID for the user/group, and hence will be the same for the same given user/group no matter what SQL Server the user/group is added to.

In order to make the sp_change_users_login synchronization step unnecessary, you will need to create the SQL Server logins on the mirror server not only with the same name, but also with the same SID as on the principal server. This can be accomplished by using the SID specification in the 'CREATE LOGIN' statement when creating the logins on the mirror server. Here is an example:

CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...

To retrieve the SID for each login from the principal server query the sys.sql_logins catalog view. Here's an example of a query that will generate an actual 'CREATE LOGIN...' statement for each SQL/Windows login on a given server:

  'create login [' + p.name + '] ' + 
  case when p.type in('U','G') then 'from windows ' else '' end +
  'with ' +
  case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
  'sid = ' + master.sys.fn_varbintohexstr(l.sid) + 
  ', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 
  'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
  case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
  else '' end +
  'default_database = ' + p.default_database_name +
  case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p 
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id 
LEFT JOIN sys.credentials c ON  l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, December 20, 2016 - 2:44:56 PM - Tim Back To Top (45012)

Thank you.  This post helped me so much and saved me hours of time.

Thursday, September 8, 2016 - 2:46:16 AM - Bharat Pagi Back To Top (43280)

Hi There,

Great post. This is something that I was in search of since couple of days. The above SP and query works very well.

Thanks a lot. Its saved my lots of time.




Sunday, July 20, 2014 - 3:54:48 AM - Chief Back To Top (32793)



We are using mirroring in SQL 2008. Last time mirror disconnected due to power failure. After power restore all servers up but by mistake we didn’t check the mirror status, after some hours when we checked the mirroring status disconnected. We resolve the issue and now mirroring status is synchronizing. Question is all the data from principle database will be mirrored to other server or mat we will lose any data? Thank you


Wednesday, July 3, 2013 - 4:04:51 PM - John Wall Back To Top (25703)

Great script.  Little documentation around for this scenario and exactly what I was looking for.

Many thanks.

Tuesday, December 11, 2012 - 3:58:26 PM - RLN Back To Top (20889)

How can I remove user access to a nirrored database without making the miror the principal?

Thursday, July 19, 2012 - 3:30:04 PM - Greg Robidoux Back To Top (18666)

Rory and benmwood - the code has been updated to make this fix as you mentioned.

Thursday, July 19, 2012 - 3:05:04 PM - Rory Back To Top (18664)

As noted by benmwood you have a couple of fields the wrong way around. Otherwise this was awesome, thanks.

Tuesday, July 7, 2009 - 1:53:31 PM - rgagne99 Back To Top (3705)

Exec this procedure on the principal to get the correct CREATE Login statements to play into the Mirror with the correct SID and hashed password.


Sunday, November 16, 2008 - 11:21:57 AM - ggrillo Back To Top (2212)

Thank you so much for this information.  I had the exact same problem and didn't understand why when I failedover to the mirror server, the application could not login.  I ran your SELECT statement to obtain the SID, applied it to the mirrored server and everything worked first time after that.  I've been working on this off and on for about 2 weeks and this was the solution. Thanks again!!  Greg

Friday, August 22, 2008 - 9:16:51 AM - ING Back To Top (1689)

Hi ,'

thanks for your replay.I know roles and access rights are kept in database level and while backup restore it was ok.

But am taking abt on mirroring failover.

1 : I configured high securiy mirroring setup.( 1 server is primary and 1 server is mirror )

2 : synchronise logins between servers 2 servers as databases are restore from backup primary server.

3 :Now mirroring setup is working fine.

But question is when i failover databases from primary to mirroring server,failover was successful but user complain that they can't access the database.

when i checked in details and i found logins,users were there but acess rights ( like datareader,datawriter,DBO) was missing ( never expected)

what could be the reason for this ?



Friday, August 22, 2008 - 4:45:13 AM - grobido Back To Top (1686)

The database rights such as db_datareader, db_datawriter, db_owner are stored within the database they are not at the server level.  So when you did your backup and restore the permissions are still there.

As far as the logins go, did you script them out of Server1 and apply them to Server2? 

If you did not and you created them manually on both ends the link between the server login and database user will be off.

You will need to run this command for each of the logins/users you can find more about it online or in SQL Server Books Online.


Another thing you could do to check these connections is to run this in your database


Friday, August 22, 2008 - 12:38:53 AM - ING Back To Top (1682)

Hi, Is there any way to migrate the database access rights,roles,etc (like DATA READER,DATA WRITER,Dtabase Owner) ? what happened is when i failed over from principal to mirroring server on one of our poduction environment under DR drill , logins was there but access rights ( like DATA READER,DATA WRITER,Dtabase Owner) was missing ( as it's very difficult to remember the access rights). Even though same thing happened when i failed over mirroring to oroginal prinicipal server. what could be the reason and How we can migrate/script the DATA READER,DATA WRITER,Dtabase Owner as well as logins, so that if anything goes wrong still we are safe. I tried on sql 2000 but am not sure in 2005. Thanks in advanced.

Tuesday, August 5, 2008 - 1:53:40 AM - benmwood Back To Top (1566)

Thanks for the tip. 

One little thing about your create login script - I think you have the Check Policy and Check Expiration values around the wrong way. You are giving the check_expiration attribute the is_policy_checked value and check_policy the is_expiration_checked value if you see what I mean?

Otherwise it was a very useful tip...thanks.

get free sql tips
agree to terms