Having to always fix logins on Always On Configuration RRS feed

  • Question

  • I have an issue with Security on my Always On DBs.

    I have 2 servers, setup w/ Always On, synchronous. 

    Application did an install, created a DB, 3 users.   The DBs were created with the default of self contained set to None.  I used the gui to add them to availability group.  It copied them over with no issues to the opposite node, and has them synchronized.  I copied the IDs from the primary server to the secondary server.  I fixed the logins so sp_change_users_login ‘Report’ showed no problems.  These are ids that are not tied to AD, so they are local SQL logins.

    My issue is now, whenever I migrate to the other node, I have to fix the logins, sql shown below.

    I have set the DBs back to the default of Self Containment = None, but that did not help.

    The vendor does a 3<sup>rd</sup> party install, and it creates the DB, ids, etc. all in the same install package, so I couldn’t precreate the DBs to be self contained. 

    Any thoughts on why I would have to fix the logins everytime?  Very frustrating problem.

    EXEC sp_change_users_login 'Report'

    EXEC sp_change_users_login 'Auto_Fix', 'IC_Admin'


    EXEC sp_change_users_login 'Auto_Fix', 'IC_ReadOnly'


    EXEC sp_change_users_login 'Auto_Fix', 'IC_User'



    Thursday, December 20, 2012 3:09 PM


  • On the servers that are not active now, drop the logins and recreate them with the SID option, and copy the SID from the other server. If you don't know the passwords, you can get them from sys.sql_logins.password_hash.

    I recommend that you discuss this with the vendor before you go ahead.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kumar muppa Friday, December 21, 2012 3:04 AM
    • Marked as answer by Maggie Luo Thursday, December 27, 2012 4:32 PM
    Thursday, December 20, 2012 10:37 PM