locked
Mirroring and orphaned users RRS feed

  • Question

  • Hi there!

    There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').

    Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.

    alter TRIGGER map_orphaned_users

    ON ALL SERVER

    FOR ALTER_DATABASE

    AS

    execute sp_change_users_login 'update_one', 'easyris_41', 'easyris_41';

    Someone an idea how to automate these usermapping after failover on the (new) principal server?

    Monday, May 29, 2006 12:27 PM

Answers

  • OK, I am going to try to write a bit.

    1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.

    2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.

    So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).

    Thanks,

    Mark

    Thursday, June 1, 2006 5:31 PM

All replies

  • Based on:

    http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx#EDBAC

    you can be set up to be notified (or run a job or particular sp) when the server becomes the principal.

    Thanks,

    mark

    Tuesday, May 30, 2006 11:20 PM
  • Hmm, maybe thats a workaround.

    But in my mind it doesn't confirm with the requirement of high availability with automatic failover. Furthermore, clients application could run into another exception (Login fails or something like that), what has to be adapted in applications what are designed for high avilibility.

    Otherway all the stuff works with domain users. So it seems like a real bug. I can't see any conceptional reason for this behavior. Hope that will be fixed in SP2...

    Torsten

    Thursday, June 1, 2006 9:36 AM
  • OK, I am going to try to write a bit.

    1. I completely agree that from a customer's point of view, if when using sql logins, if the database fails over and the clients cannot get to the database (for whatever reason. Here it is a mixed mapping from login on a different server to a user in a "new" database ), then the system is not "highly available." So, we haven't made it easy to use mirroring in certain scenarios. I may try to create a script that solves this problem this weekend.

    2. On the other hand, this problem has been here at least since 7.0. In log shipping, backup/restore, and attach/detach this problem of moving the database to a different server where the meta data in master and msdb are different on different servers. The problem is that we have made moving a database to a different server so much easier that the problems that were small earlier are becoming bigger.

    So, this really isn't a bug IMHO, but it is unexpected behavior (which is still bad from a customer's point of view).

    Thanks,

    Mark

    Thursday, June 1, 2006 5:31 PM
  • I am just starting to look into mirroring and had run across this same problem.  I have not had a chance to look into this but supposedly there is a task in SSIS that allows you to transfer logins from one server to another.
    Wednesday, June 7, 2006 2:12 AM
  • Hi guys,

    I've asked MS about using sqlusers on a mirrored database and got this reply from Hugo Nunes, support Engineer at Microsoft.

    In order to avoid orphaned users on the mirror database you’ll have to copy the logins corresponding to the database users, to the standby server. The copy must be made before restoring the database on the standby server. You’ll also need to copy the user with his SID. The Transfer Logins Task from SSIS performs that task for you as long you set the CopySids property to true.

    It works.

     

    Tuesday, June 20, 2006 1:35 PM
  • yes. but it doesn't solve the issue of default database for a login,

    which leads to the same issue - login does not work.

    Tuesday, November 14, 2006 9:45 AM
  • Hi to All,

     

    We are currently having issues like that before, the fix that we do is adding a short dynamic script that will remove orphaned user by executing sp_change_users_login 'update_one', '<user1>','<user1>'  on the mirrored database everytime a switch occur.

    hope this helps!

     

    regards,

    Thursday, May 10, 2007 10:10 AM
  • Hi Anders,

    Did you ever got this to work?  Do you know of any documents that walk you thought on how to do this?  I am new with SSIS.

     

    Thanks

    Rick

    Sunday, May 20, 2007 9:05 PM
  • Hi Rick,

     

    Yes, it's been up and running for almost a year now.

     

    Im sorry i dont have a walk through for you but the SSIS bit is pretty straight forward.

    These documents should get you started.

     

    Transfer Logins Task

    http://msdn2.microsoft.com/en-us/library/ms137870.aspx

     

    Set Task Properties

    http://msdn2.microsoft.com/en-us/library/ms139733.aspx

     

     

    /Anders

    Monday, May 21, 2007 5:26 PM