SQL 2005 Snapshot Security

Question

In SQL Server 2005, a snapshot of a database can be created that allows read-only access to a database, even when the database is in "recovery pending" mode. One use case for this capability is in creating a reporting database that references a copy of a production database, which is kept current through log-shipping.

In this scenario, how can I implement security on the "snapshot" database that is different from the "production" source database?

For example, in the production database, all access to data is through stored procedures, while in the snapshot database users are allowed to select from table in the database for reporting purposes. The problem the I see is that security for the snapshot database is inherited from the source database, and can not be changed because snapshots are strictly read-only.

Answer 1

Are you able to manage permissions on this database? Would adding a separate user who only has read access to a database be sufficient for this type of scenario? This could be a read-only user on the main database, but is only effectively used on the snapshot db.

i.e. Add a new user, readerMan5000 who is only given select access, to the database in question. Then require users to authenticate through that new credential.

Note to future commenters, you may want to read: http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/ or http://msdn.microsoft.com/en-us/library/ms187054(SQL.90).aspx before you open your big mouth like me. :)

Answer 2

You can't change permissions after you take the snapshot, but here's one workaround: instead of having them access the tables directly, require them to use views instead. If the views are used only for reporting, then you can set tight security on them in the original database, and then have the users hit those views in the snapshot. You'll need to restrict access on the underlying tables though if you want it to be effective.