SQL 2005 快照安全
在 SQL Server 2005 中，可以创建数据库快照，以允许对数据库进行只读访问，即使数据库处于“恢复挂起”模式也是如此。 此功能的一个用例是创建引用生产数据库副本的报告数据库，该数据库通过日志传送保持最新状态。
在这种情况下，如何在与“生产”源数据库不同的“快照”数据库上实现安全性？
例如，在生产数据库中，对数据的所有访问都是通过存储过程进行的，而在快照数据库中，允许用户从数据库中的表中进行选择以用于报告目的。 我看到的问题是快照数据库的安全性是从源数据库继承的，并且无法更改，因为快照是严格只读的。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（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.
您能够管理该数据库的权限吗？ 对于这种类型的场景，添加一个仅具有数据库读取访问权限的单独用户是否足够？ 这可以是主数据库上的只读用户，但仅在快照数据库上有效使用。
即添加一个新用户 readerMan5000，该用户仅被授予对相关数据库的选择访问权限。 然后要求用户通过该新凭据进行身份验证。
请注意未来的评论者，您可能想阅读：
http://www.simple-talk.com /sql/数据库管理/sql-server-2005-snapshots/
或者
http://msdn.microsoft.com/en-us /library/ms187054(SQL.90).aspx
在你像我一样张开你的大嘴之前。 :)
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. :)