Post reply

Log Shipped DB Access

  • June 16, 2014 at 9:37 am

    #294441

    All,

    I need to give a SQL account read access to a log shipped db. Encoiuntering issues due to it being read-only.

    How do I achieve this, unable to fix any orphaned users in the secondary log shipped db also due to read only.

    Thanks

    T.

  • June 16, 2014 at 12:08 pm

    #1722066

    Talib123 (6/16/2014)

    All,

    I need to give a SQL account read access to a log shipped db. Encoiuntering issues due to it being read-only.

    How do I achieve this, unable to fix any orphaned users in the secondary log shipped db also due to read only.

    Thanks

    T.

    Does the user have access to the original database?

  • June 17, 2014 at 2:34 am

    #1722186

    When you set up the log shipping you can set it to put the secondary into Standby mode rather than No Recovery mode. If you do that you will be able to connect to the secondary on a read-only basis. However, when each transaction log is restored, your read-only connections will be killed off with no warning, so you probably wouldn't want to do that if you're shipping every minute, for example.

  • June 18, 2014 at 9:38 am

    #1722854

    I found the answer.

    The account was created on the Primary server. However to resolve the SID for the account on the primary must be the same as the one on the secondary when the account is created at the server level.

    sp_addlogin 'account', @passwd='pwd1', @sid=SID

    go

    SID = sid of Primary Account

    This basically resolves the orphaned user.

    Thanks for your replies.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply