question

sreekanth99 avatar image
sreekanth99 asked

How to create a login that should access only database snapshot ?

How to create a login that should access database snapshot ?not accessing orginal databse
database-snapshot
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
raadee avatar image
raadee answered
You can do like this. 1. Create a login on the principal server and add permissions to the database that you create a snapshot of. 2. Script out the created login with the script I attached [link text][1], run the script against your database (use databasename), copy the row that has the create login statement for your new login. 3. Run the copied text (create login) against the mirror server to create the login. 4. Delete the login you created on the principal server (do not remove the user permissions from the database) Now you have a user that can access the snapshot. Since you removed the login from the principal server this user can't login and access the principal database. And since the login and database permissions exist on the mirror server the user can access the snapshot. Logins are stored in the Master database which is not mirrored, so this trick can be used to to access snapshots. [1]: /storage/temp/ 2343-security-transfer-logins.txt

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sreekanth99 avatar image sreekanth99 commented ·
thanks. but its useful when database mirroring configured.if with out mirroring how to give a permission to a login that should accessible only snapshot not database.is there any database roles are for accessing snapshot
0 Likes 0 ·
raadee avatar image raadee sreekanth99 commented ·
Sorry, I assumed it was a mirror config. If it is only a snapshot locally then I do not think it is possible. Microsoft: "A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots." Maybe somebody else has a trick to teach us?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.