locked
How to repair an orphaned user within a read-only standby database maintained by log shipping RRS feed

  • Question

  • Configuration:

    DB1 is a 2TB database running on SQL Server 2008 R2 SP1 x64 Enterprise

    We maintain a copy of DB1 on another server using log shipping in stand-by mode on SQL Server 2008 R2 SP1 x64 Standard

    Situation:

    There is a login/user in DB1 called "readonly".  This login/user is orphaned in the standby database.  When I try to repair the orphan using EXEC sp_change_users_login 'auto_fix', 'readonly'Problem:  How do you repair an orphaned user in this situation?

    I get the error:

    The row for user 'readonly' will be fixed by updating its login link to a login already in existence.
    Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 242
    Failed to update database "DB1" because the database is read-only.

    Friday, October 5, 2012 8:25 PM

Answers

  • Hello,

    There isn't a way to do anything in the database. What you'll need to do is drop the LOGIN and use either an SSIS task to the procedure outlined in the link below to create the login with the same SID as the instance is came from. When the login is created with the same SID that it had on the other instance, the database user isn't "orphaned" because the SIDs match :)

    http://support.microsoft.com/kb/918992

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    • Proposed as answer by TiborKMVP Saturday, October 6, 2012 5:54 PM
    • Marked as answer by Shulei Chen Tuesday, October 16, 2012 1:45 AM
    Friday, October 5, 2012 9:20 PM
    Answerer

All replies

  • Hello,

    There isn't a way to do anything in the database. What you'll need to do is drop the LOGIN and use either an SSIS task to the procedure outlined in the link below to create the login with the same SID as the instance is came from. When the login is created with the same SID that it had on the other instance, the database user isn't "orphaned" because the SIDs match :)

    http://support.microsoft.com/kb/918992

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    • Proposed as answer by TiborKMVP Saturday, October 6, 2012 5:54 PM
    • Marked as answer by Shulei Chen Tuesday, October 16, 2012 1:45 AM
    Friday, October 5, 2012 9:20 PM
    Answerer
  • Hello,

    There isn't a way to do anything in the database. What you'll need to do is drop the LOGIN and use either an SSIS task to the procedure outlined in the link below to create the login with the same SID as the instance is came from. When the login is created with the same SID that it had on the other instance, the database user isn't "orphaned" because the SIDs match :)

    http://support.microsoft.com/kb/918992

    -Sean


    Sean Gallardy, MCC | Blog | Twitter



    Santi Florian Senior Computer Consultant

    Friday, November 23, 2012 6:52 AM