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

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

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

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

 

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

1.        Insert,Update,Delete 触发器

2.        Check 约束 和 Delete 触发器

3.        设置数据库为只读

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

5.        拒绝对象级别权限

6.        创建视图
 

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

[sql]  

create database MyDB  

create table tblEvents  

(  

  id int,  

  logEvent varchar(1000)  

)  

insert into tblEvents  

values (1, 'Password Changed'), (2, 'User Dropped'), (3,'Finance Data Changed')  

 

insert/Update/Delete触发器:
 

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

[sql]  

CREATE TRIGGER trReadOnly_tblEvents ON tblEvents  

    INSTEAD OF INSERT,UPDATE,DELETE  

AS  

BEGIN  

    RAISERROR( 'tblEvents table is read only.',16, 1 )  

    ROLLBACK TRANSACTION  

END  

 

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

Msg 50000, Level 16, State 1, ProceduretrReadOnly_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 beenaborted.

 

2 使用Check 约束和Delete 触发器:
 

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

首先,先禁用在上一步创建的触发器:disable trigger trReadOnly_tblEvents on tblevents

然后,添加约束:ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINTchk_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触发器

[sql]  

CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents 

    INSTEAD OF     www.2cto.com 

              DELETE  

AS  

BEGIN  

    RAISERROR( 'tblEvents table is read only.',16, 1 )  

    ROLLBACK TRANSACTION  

END  

 

设置数据库为只读:

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

[sql]  

USE [master]  

GO  

ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT  

GO  

 

把表放到只读文件组:

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

[sql]  

USE [master]  

GO  

ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS] 

GO  

ALTER DATABASE [MyDB] ADD FILE ( NAME =N'mydb_readonly_tables', FILENAME = N'C:\JSPACE\myDBReadOnly.ndf' , SIZE =2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READ_ONLY_TBLS]  

GO  

DROP table tblEvents  

create table tblEvents  

(  

id int,  

logEvent varchar(1000)  

)    www.2cto.com  

ON [READ_ONLY_TBLS]  

ALTER DATABASE [MyDB] MODIFY FILEGROUP [READ_ONLY_TBLS]READONLY  
 

任何对表的DML操作都会被拒绝,并返回以下错误信息:  

Msg 652, Level 16, State 1, Line 1  

The index "" for table"dbo.tblEvents" (RowsetId 72057594038845440) resides on a read-onlyfilegroup ("READ_ONLY_TBLS"), which cannot be modified.  

 

拒绝对象级别权限

可以通过DCL命令控制用户权限,但此步无法限制高级权限用户(如system admin,DatabaseOwner):

[sql]  

DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal  

DENY INSERT, UPDATE, DELETE ON tblEvents TO Public  

 

创建视图

为了替代直接访问表,可以使用视图:

[sql]  

create view vwtblEvents  

as  

select ID, Logevent from tblEvents  

union all  

select 0, '0' where 1=0  

  www.2cto.com  

在这个视图中,使用了UNION,只有在你确保有对应数量的列时才使用。在这个例子中,表有两列,所以使用两个输出列。同时,你也应该确保数据类型也一致。

当一个用户尝试通过INSERT/UPDATE/DELETE操作数据时,将得到以下错误信息:
 

Msg 4406, Level 16, State 1, Line 1

Update or insert of view or function 'vwtblEvents1'failed because it contains a derived or constant field.

Msg 4426, Level 16, State 1, Line 1

View'vwtblEvents1' is not updatable because thedefinition contains a UNIONoperator.

 

最后一步:

确认是否有必要用这些步骤来设置表为只读。

如果一个表总是只读,那么你应该放到只读文件组中。

 

 

作者 黄钊吉

 

<think>嗯,用户想了解如何在SQL Server中将数据库设置只读模式。首先,我需要回忆一下之前学过的相关知识。记得可以通过SSMS图形界面或者T-SQL命令来设置。另外,可能还涉及到权限的问题,比如需要确保用户有足够的权限来修改数据库属性。 首先,使用SSMS的话,应该是在数据库属性里的选项中有一个“只读”选项,设置为True即可。但可能需要检查一下数据库的状态,确保它不在使用中,或者是否有其他连接导致无法修改。另外,用户提到的引用里,引用[2]和[4]提到在数据库属性的选项里修改“数据库只读”为False,那反过来设置为True应该就是设为只读模式了。 然后是T-SQL方法,应该用ALTER DATABASE语句,设置READ_ONLY。比如ALTER DATABASE [数据库名] SET READ_ONLY WITH NO_WAIT; 但需要确保没有其他用户连接,否则可能需要先设置为单用户模式,再修改。 另外,权限方面,引用[3]提到创建只读用户,可以通过授予SELECT权限,而不是直接修改数据库只读。这可能也是一种方法,但用户的问题是设置数据库只读模式,所以可能更倾向于前者。 还要注意,附加数据库时可能出现只读的情况,如引用[1]和[4]中提到的,可能和文件权限有关,比如MDF和LDF文件的权限是否正确,确保SQL Server服务账户有完全控制权。但设置数据库只读模式的话,可能不需要处理文件权限,除非遇到问题。 需要分步骤说明,可能先介绍图形界面方法,再是T-SQL命令,然后是权限调整。同时,注意事项里要提到检查服务账户权限,处理活动连接,以及只读模式的影响,比如不能进行写操作。 用户可能遇到的问题包括设置后仍然可写,这时候需要检查是否成功应用,或者是否有其他覆盖设置的情况。此外,可能想了解只读模式的应用场景,比如数据归档或者防止误操作,这些可以作为相关问题的建议。</think>### SQL Server设置数据库只读模式的方法教程 #### 一、通过SQL Server Management Studio (SSMS) 图形界面设置 1. **连接数据库实例** 打开SSMS后,右键点击目标数据库,选择“属性”。 2. **修改数据库选项** 在属性窗口中,选择“选项”标签页,找到“状态”部分的“数据库只读”选项,将其值从`False`改为`True`,点击“确定”保存[^2][^4]。 3. **检查数据库状态** 设置完成后,可通过以下命令验证: ```sql SELECT name, is_read_only FROM sys.databases WHERE name = '数据库名'; ``` #### 二、通过T-SQL命令设置 1. **直接设置只读模式** ```sql ALTER DATABASE [数据库名] SET READ_ONLY WITH NO_WAIT; ``` 此命令会立即生效,但需确保没有活跃事务或用户连接[^4]。 2. **处理活动连接** 若存在活动连接,可先强制断开: ```sql ALTER DATABASE [数据库名] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE [数据库名] SET READ_ONLY; ALTER DATABASE [数据库名] SET MULTI_USER; ``` #### 三、通过权限控制实现只读访问 若需允许特定用户仅拥有查询权限(不修改数据库模式): 1. **创建只读用户** ```sql CREATE LOGIN [readonly_user] WITH PASSWORD = '密码'; USE [数据库名]; CREATE USER [readonly_user] FOR LOGIN [readonly_user]; GRANT SELECT TO [readonly_user]; ``` 此方法通过权限隔离实现只读操作[^3]。 #### 四、注意事项 1. **文件权限问题** 若数据库文件(.mdf/.ldf)的NTFS权限未授予SQL Server服务账户完全控制权,可能导致设置失败。需右键文件→属性→安全→编辑权限[^1][^4]。 2. **只读模式的影响** 设为只读后,无法执行INSERT、UPDATE、DELETE等写操作,但允许查询和索引维护。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值