Results 1 to 8 of 8

Thread: Database snapshot using a mirror

  1. #1
    Join Date
    Aug 2004
    Posts
    188

    Database snapshot using a mirror

    Hi all, I have a few questions about permissions connecting to a snapshot of a database mirror.

    Here is my set up. I have two servers principal and mirror they are different servers. I have created a snapshot of the mirrored db on the server that has the mirrored database.

    When I log into the principal server and log into SSMS as userA I can query the principal database without any issues.

    When I log into the mirrored server and connect with the same user I can see the snapshot in SSMS but cant loginto it.

    It's telling me its not accessable. But when I log in to the mirror using SA I can see the snapshot and query it without any issues. I then changed the login userA permissions and made it an sysadmin on the mirrored server. I then connected using SSMS as userA and could query the snapshot. Is there something Im missing permission wise? Or do the users have to be sysadmins on the mirror to query the snapshot.

    Thanks for your time.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    If your server login and database user ID synchronized in mirror server.

  3. #3
    Join Date
    Aug 2004
    Posts
    188
    Quote Originally Posted by skhanal View Post
    If your server login and database user ID synchronized in mirror server.
    Hi, how would you go about doing that?

  4. #4
    Join Date
    Aug 2004
    Posts
    188
    Quote Originally Posted by ezecastle View Post
    Hi, how would you go about doing that?
    OK I think I have it... I broke the mirror. I then brought the mirror on line and then used rev_login to get the users from the principal db then recreated them on the mirrored db. Took backups of the principal and restored them to the mirror db. Then I recreated the mirror and snapshot and now I'm able to query the snapshot without having userA as a sysadmin.

    Thanks.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You could have used sp_change_users_login to update logins.

  6. #6
    Join Date
    Jan 2010
    Posts
    37
    OK I think I have it... I broke the mirror. I then brought the mirror on line and then used rev_login to get the users from the principal db then recreated them on the mirrored db. Took backups of the principal and restored them to the mirror db. Then I recreated the mirror and snapshot and now I'm able to query the snapshot without having userA as a sysadmin.
    But doesn't that break the functionality of your mirror? Did you recreate the mirror after you fixed access to the snapshot? Otherwise I imagine you will have to have some other kind of mechanism in place to keep the data in the databases somewhat in sync like an SSIS package or BCP job.

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    >> Then I recreated the mirror and snapshot and now I'm able to query the snapshot without having userA as a sysadmin.

    Did you see OP's post?

  8. #8
    Join Date
    Jan 2010
    Posts
    37
    Did you see OP's post?
    NNNNope. Sorry about that. My mistake.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •