locked
Manual Failover removes Login/DB User mappings RRS feed

  • Question

  • Hi!

    I have just managed to configure two separate servers as Principal and Mirror for one database. In this database their exist a predefined role that has been granted specific rights on tables, SP and operations. In this database I created a specific user that belongs to this role through a SQL Server login, and so finally there is a SQL Server login is mapped to the created user in the database. The client's can login and all seems to be working fine. The clients have updated connections strings with "Failover Partner" specified, but they fail to login after a manual failover.

    The reason that they fail, is that when I do a manual failover to the mirroring server and then eventually back, the SQL Server Login no longer has the user mappings as it had before. When I open the SQL Server Login's properties in the "management studio" and select page "User Mappings" no database is selected for the SQL Server Login, and its user and the specific role is no longer selected. 
    Why?
    Have I missed something in the setup of the mirroring, surely this can't be right?
    As I remember there is nothing in BOL that describes anything about this behavior!!

    Some basic for both server:
    Windows Server 2003 SP2
    SQL Server 2005 Standard Edition SP3

    /Best Regards

    Tuesday, June 15, 2010 9:04 PM

Answers

  • Anders,

    Adding to Xiao Explantion , Instead of creating sql login manually on mirror server i suggest you to script out the logins on primary server using sp Sp_revlogin and than execute the o/p of the sp on mirrorserver and sync the orphan logins using sp _change_users_login on mirror server.

    The reason if you create logins manually on mirror server the SID will be different from that of the primary server for the corresponding logins . so it is good practice to script out the logins through Sp_revlogin .

    -----------------------------
    Thanks,Suhas V
    • Proposed as answer by martenrune Wednesday, June 23, 2010 7:27 AM
    • Marked as answer by Tom Li - MSFT Thursday, June 24, 2010 1:46 AM
    Thursday, June 17, 2010 9:22 AM
  • Hi,

    Do you create the SQL Server login and map the database user to the login both in the principal database and mirror database? If you only backup the principal database and restore it on the mirror server, the SQL Server logins in the principal server cannot be copied to the mirror server. To work around the issue, you need to also create the SQL Server logins and map the database user to the logins after restoring the principal database on the mirror server.

    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by Tom Li - MSFT Thursday, June 24, 2010 1:46 AM
    Thursday, June 17, 2010 8:48 AM

All replies

  • Hi,

    Do you create the SQL Server login and map the database user to the login both in the principal database and mirror database? If you only backup the principal database and restore it on the mirror server, the SQL Server logins in the principal server cannot be copied to the mirror server. To work around the issue, you need to also create the SQL Server logins and map the database user to the logins after restoring the principal database on the mirror server.

    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by Tom Li - MSFT Thursday, June 24, 2010 1:46 AM
    Thursday, June 17, 2010 8:48 AM
  • Anders,

    Adding to Xiao Explantion , Instead of creating sql login manually on mirror server i suggest you to script out the logins on primary server using sp Sp_revlogin and than execute the o/p of the sp on mirrorserver and sync the orphan logins using sp _change_users_login on mirror server.

    The reason if you create logins manually on mirror server the SID will be different from that of the primary server for the corresponding logins . so it is good practice to script out the logins through Sp_revlogin .

    -----------------------------
    Thanks,Suhas V
    • Proposed as answer by martenrune Wednesday, June 23, 2010 7:27 AM
    • Marked as answer by Tom Li - MSFT Thursday, June 24, 2010 1:46 AM
    Thursday, June 17, 2010 9:22 AM