asked
how to grant read access on a snapshot database (which is from a mirror)
Hi, The following situation: We have database A as a principal on sever S1. This database is mirrored to server S2. We create a snapshot on server S2 for this database (let's call this database B) For reporting purposes, I want to have user U1 read access to database B (role db_reader). But since database B is read only, it seems to be impossible. I already tried to create user U1 on server A and recreated snapshot B, but that doesn't seem to work either. Am I doing something wrong or is this not possible?
The database is READ_ONLY so everyone will access it as read only, despite what their permissions are in the production database. There is no need to set a user to read only in this case.
ok, but I cannot map him to the snapshot: (failed to update database "B" because the database is read-only.
Ah, got it. It's a 'new' user just to access the snapshot ... can they not be added to the production database?
If it works, but what are the steps? these: 1) create on server S1 with readonly access on database A 2) account automatically mirrored on server S2? 3) are rights inherited to the snapshot? (after a recreate)
That's what I'd expect. Not tried it here as we have mirror for HA and log shipping for OLAP. Can easily be reversed out if it isnt what you need.
What authentication are the production/snapshot instances using? Just wondering if you could create a user in the database on prod but prevent them connecting with a server level deny and then allow it on the snapshot. If you are concerned about incorrect/improper connection.
so, IF its required, you could use a group membership to deny connect access to production instance and allow read access to the production database. Then, on the snapshot instance, allow the group connect access and the read access will already be in the database. This is based on speculation - as I say I cant test it here. Try it out before deploying to the real user
