Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
 Posted - 2010-01-20 : 12:36:04
|
| Does anyone have a way to grant read-only access to a database snapshot created on a mirror WITHOUT granting read-only access to the actual database itself?=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-20 : 14:00:59
|
| Perhaps I misunderstand what you're trying to accomplish Graz, but isn't a snapshot already read only...? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-20 : 14:09:43
|
| ^ ignore me...i understand. you want read_only access for a user not already granted access to original db...?can't flip the read_only bit on a snapshot so i don't think it's possible. interesting. definitely should be fun poking around trying.EDIT: Ugly workaround (you probably already thought of this) but the script that creates the snapshot could have 3 steps (assuming this is a domain user):1. Add user to original db2. Create snapshot3. Drop user on original dbYou can deny the account login privilege on principal server so that there is no time when the database user has access to the principal. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2010-01-20 : 14:36:01
|
| That's what I've been thinking so far. But I really, really don't like it.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-01-20 : 17:15:03
|
| You cannot alter privileges on a database that is created as snapshot. You can drop the current snapshot, create/grant privileges in source database, take new snapshot. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 02:57:47
|
| Can you have VIEWS in another DB (READERS_DB) that are pointing at the SNAPSHOT DB? Or would users on READERS_DB have to have underlying SELECT on the SNAPSHOT_DB? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 02:58:41
|
| Also: LinkedServer - which can have different login credentials at the far end, then use OPENQUERY or LinkedServer.SnapShotDB.dbo.Table ?? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-21 : 10:05:17
|
quote:
Originally posted by Kristen
Can you have VIEWS in another DB (READERS_DB) that are pointing at the SNAPSHOT DB? Or would users on READERS_DB have to have underlying SELECT on the SNAPSHOT_DB?
Doesn't work because the user doesn't have access to the snapshot db. synonym doesn't work either. it's an interesting problem i think. |
|
|
|
|
|
|
Read-only access to a snapshot on a mirror