1. 爱易网页
  2. 数据库教程
  3. 设立SQLServer数据库中某些表为只读的多种方法

设立SQLServer数据库中某些表为只读的多种方法

日期：2014-05-16　 浏览次数：21799 次

设置SQLServer数据库中某些表为只读的多种方法

翻译自：http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012614

在某些情况下需要把SQLServer的表设为只读，下面举出几种方法：

一般情况下会有几种情况需要你把数据库设为只读：

1.        Insert,Update,Delete 触发器

2.        Check 约束 和 Delete 触发器

3.        设置数据库为只读

4.        把表放到只读文件组中

5.        拒绝对象级别权限

6.        创建视图

在开始之前，先创建一个数据库及表作为示例：


nsert/Update/Delete触发器：

请注意这里使用的是INSTEADOF trigger，因为如果你使用了AFTER trigger，会在执行DELETE, UPDATE和INSERT语句时请求锁，会对写事务日志和回滚操作造成性能上的影响


当用户执行insert/update/delete时，将提示以下错误：

Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7
tblEvents table is read only.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

使用 Check 约束和Delete 触发器：

现在先在表中添加一个check 约束“1=0”，意味着总是失败。它禁止你在任何行执行INSERT或者Delete操作。

首先，先禁用在上一步创建的触发器：disable trigger trReadOnly_tblEvents on tblevents
然后，添加约束：ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )

执行以后，无论你执行任何一个INSERT/UPDATE语句，都将提示以下错误信息：

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECKconstraint "chk_read_only_tblEvent". The conflict occurred indatabase "MyDB", table "dbo.tblEvents".
The statement has been terminated.

但是，该约束不会对DELETE操作造成影响，为此，需要再创建一个DDL触发器：


设置数据库为只读：

你可以设置数据库为只读，这样就禁止对整个数据库的DDL/DML操作。可以使用以下语句：


把表放到只读文件组：

可以在一个只读文件组中创建一个表：