Database snapshot access - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database snapshot access

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-22 : 05:51:49

Hello Experts,

We have an OLTP environment, which is mirrored to a different server. And to read and run reports on the mirrored DB, we have created snapshot on it.

Now my customer wants to create some DB logins, which can only access the snapshot. I tried giving dbread access to the snapshot but it throws below error.

"Failed to update database "TestSachin_SNAP" because the database is read-only."

Please help. How can we give access to snapshot only, and restricting access to other database and objects in the server.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-22 : 11:02:33

All permissions on the snapshot must exist in the source database at the time the snapshot is created.

CODO ERGO SUM

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-22 : 13:30:06

Thanks Michael, I tried creating a fresh snapshot but it still didn't worked. I removed the mirror and then tried again but no luck.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-22 : 14:08:57

Did you create the permissions in the snapshot source database?

CODO ERGO SUM

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-23 : 10:20:51

Thanks Micheal,

I granted the user "dbread" permission in the principal db, and then recreated mirror on a different server with fresh backup.

Also created fresh snapshot. But nothing works.

regards
sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-23 : 10:34:57

Is the login on the mirror server? Does the SID match the login on the principal? It must.

--
Gail Shaw
SQL Server MVP

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-24 : 09:50:27

Hi Gail,

No the SIDs are not matching. What can I do to sync them?

regards
sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-24 : 11:04:19

Drop the login that you created on the mirror server and recreate it with the correct SID. There's syntax in the CREATE LOGIN statement to specify the SID.

--
Gail Shaw
SQL Server MVP

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-28 : 05:55:08

Thanks a lot Gail. It worked :)

Don't sit back because of failure. It will come back to check if you still available. -- Binu

alzdba
Starting Member

10 Posts

Posted - 2010-10-26 : 05:16:33

Did you restrict "access only to snapshot" ?
How did you do that ?
- Using a db-trigger challenging a db-role membership (e.g. urole_snap_only) ?

- ?

Just in case your principal fails and your mirrordb becomes primary, your users may be able to access your data directly on the source db of your snapshot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources