Post reply

How do I create an ID to read a Log Shipped database (read only)

  • October 8, 2010 at 12:04 pm

    #147273

    Hi all:

    I have a source and a destination using Log shipping SQl Server 2005. Now the destination is in read-only mode.

    I created and ID with read only on the source DB, wait to be log shipped to the destination, then create the ID on the destination, trying to grant read privilege is not possible because the DB is in read only mode. So, I just created the ID and STILL can not access (read) the Log Shipped database.

    Can anybody point me to a link where they have a solution for this?

    How can I Log Ship my database with read only and not be able to read it.

    Thanks

  • October 8, 2010 at 12:06 pm

    #1234435

    Create the user on the primary. When you create the login on the secondary, make sure that the SID matches with the login on the primary.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 8, 2010 at 2:02 pm

    #1234512

    Article that explains how to retain the SID when transferring logins between servers.

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

  • October 8, 2010 at 2:04 pm

    #1234513

    Just a note in addition to Gail's post:

    Create the login on both servers, preserving the SID, then grant the user whatever read permissions you want them to have on the Source server, and then at the next log backup/restore, it will have carried over to the destination.

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

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