翻譯自： 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的表設(shè)為只讀，下面舉出幾種方法：

一般情況下會(huì)有幾種情況需要你把數(shù)據(jù)庫(kù)設(shè)為只讀：

1.????????Insert,Update,Delete 觸發(fā)器

2.????????Check 約束 和 Delete 觸發(fā)器

3.????????設(shè)置數(shù)據(jù)庫(kù)為只讀

4.????????把表放到只讀文件組中

5.????????拒絕對(duì)象級(jí)別權(quán)限

6.????????創(chuàng)建視圖

在開(kāi)始之前，先創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)及表作為示例：

    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')
  

nsert/Update/Delete觸發(fā)器：

請(qǐng)注意這里使用的是INSTEADOF trigger，因?yàn)槿绻闶褂昧? AFTER trigger ，會(huì)在執(zhí)行 DELETE, UPDATE和INSERT語(yǔ)句時(shí)請(qǐng)求鎖，會(huì)對(duì)寫(xiě)事務(wù)日志和回滾操作造成性能上的影響

    CREATE TRIGGER trReadOnly_tblEvents ON tblEvents

    INSTEAD OF INSERT,

               UPDATE,

               DELETE

AS

BEGIN

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

    ROLLBACK TRANSACTION

END


  

當(dāng)用戶(hù)執(zhí)行insert/update/delete時(shí)，將提示以下錯(cuò)誤：

    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 觸發(fā)器：

現(xiàn)在先在表中添加一個(gè)check 約束“1=0”，意味著總是失敗。它禁止你在任何行執(zhí)行INSERT或者Delete操作。

    首先，先禁用在上一步創(chuàng)建的觸發(fā)器：disable trigger trReadOnly_tblEvents on tblevents
  

    然后，添加約束：ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )
  

執(zhí)行以后，無(wú)論你執(zhí)行任何一個(gè)INSERT/UPDATE語(yǔ)句，都將提示以下錯(cuò)誤信息：

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.

但是，該約束不會(huì)對(duì)DELETE操作造成影響，為此，需要再創(chuàng)建一個(gè)DDL觸發(fā)器：

    CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents

    INSTEAD OF 

               DELETE

AS

BEGIN

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

    ROLLBACK TRANSACTION

END


  

設(shè)置數(shù)據(jù)庫(kù)為只讀：

    USE [master]

GO

ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT

GO


  

把表放到只讀文件組：

    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)

)

ON [READ_ONLY_TBLS]

ALTER DATABASE [MyDB] MODIFY FILEGROUP [READ_ONLY_TBLS] READONLY

任何對(duì)表的DML操作都會(huì)被拒絕，并返回以下錯(cuò)誤信息：

Msg 652, Level 16, State 1, Line 1

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


  

拒絕對(duì)象級(jí)別權(quán)限

    DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal

DENY INSERT, UPDATE, DELETE ON tblEvents TO Public


  

創(chuàng)建視圖

為了替代直接訪(fǎng)問(wèn)表，可以使用視圖：

    create view vwtblEvents

as

select ID, Logevent from tblEvents

union all

select 0, '0' where 1=0


  

在這個(gè)視圖中，使用了UNION，只有在你確保有對(duì)應(yīng)數(shù)量的列時(shí)才使用。在這個(gè)例子中，表有兩列，所以使用兩個(gè)輸出列。同時(shí)，你也應(yīng)該確保數(shù)據(jù)類(lèi)型也一致。

當(dāng)一個(gè)用戶(hù)嘗試通過(guò)INSERT/UPDATE/DELETE操作數(shù)據(jù)時(shí)，將得到以下錯(cuò)誤信息：

    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
  

最后一步：

確認(rèn)是否有必要用這些步驟來(lái)設(shè)置表為只讀。

如果一個(gè)表總是只讀，那么你應(yīng)該放到只讀文件組中。

設(shè)置SQLServer數(shù)據(jù)庫(kù)中某些表為只讀的多種方法