locked
SQL 2008 mirroring and orphaned users RRS feed

  • Question

  • Trying to mirror a SQL 2008 DB.  The DB is mirroring and failing over properly but always has log in failures.  Seemed it was an "Orphaned User" issue.  The DB once failed over did indeed show it had an orphaned user.  Used this command to resolve the user on the secondary server and it said it was happy.  Changed the PW and made sure the user was enabled but the program was still unable to connect to the DB

    USE

    Now, once the above was done and I failed back to the primary the same "Bad Password" and orphaned user stuff started happening on what used to be the working DB.  Once again used the above command on the primary server and then sure enough connection to the DB worked.

    So, what is the trick to mirroring a DB with a local SQL login out to a secondary server and avoid this login / orphaned user issue.  Do I need to delete the login on the secondary sever and then script, create to, file, from the primary SQL server and then fail over to the secondary so the DB is active and then run the scripted user create and make sure the PW is the same as the primary?

    Sorry for such a long post but want to give the details of this and what I tried.  Thanks for any help you can provide!!
    Have a happy New Year!

    DB_NAME
    GO
    sp_change_users_login @Action='update_one', @UserNamePattern='igadmin',
    @LoginName
    ='igadmin'
    GO
    • Edited by Transam388 Thursday, December 30, 2010 10:23 PM remove spaces
    Thursday, December 30, 2010 10:22 PM

Answers

  • This problem is caused by the SSIDs not matching up between logins on both instances, and therefore mismatching with the associated database user. Have a look at the following article for how to sync. them...

    http://www.mssqltips.com/tip.asp?tip=1166

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005), MCAD, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by Transam388 Monday, January 3, 2011 12:45 PM
    Friday, December 31, 2010 6:38 AM

All replies

  • This problem is caused by the SSIDs not matching up between logins on both instances, and therefore mismatching with the associated database user. Have a look at the following article for how to sync. them...

    http://www.mssqltips.com/tip.asp?tip=1166

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005), MCAD, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by Transam388 Monday, January 3, 2011 12:45 PM
    Friday, December 31, 2010 6:38 AM
  • The problem is in SID, you're users have to have the sam sid in both servers, you can create the same user in both database and if they have the sam sid, no more oprhaned users ;)

    Friday, December 31, 2010 2:55 PM
  • Thanks to both of you and "Moort" for the link you provided.  The script that the link has which basically creates users copied from the primary server worked like a charm!!
    Monday, January 3, 2011 12:57 PM