对指定的表设置只读属性

通常，要让一个表只读，我们可以：

1. 创建Insert/Update/Delete触发器
2. 创建检查约束来禁用Insert/Update，创建触发器禁用Delete
3. 设置表所在的数据库只读
4. 把表放到只读文件组中
5. 拒绝指定的用户有Insert/Update/Delete的权限
6. 通过视图

但是上述方案中，有些可能会给我们带来一些不便：

1. 对大表使用触发器会带来性能影响
2. 对dbo角色不能移除DML权限
3. 对整个数据库设置只读时常不现实
4. 放在只读文件组中的表仍然可以添加和删除列

下面是采用方案中的第2种来实现，实现的存储过程如下：

 

USE [TESTDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_Util_SetTableReadOnly]    Script Date: 2013/7/31 16:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================================================
--  对任何指定的表设置是否只读
-- =================================================================================================
ALTER PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
  @tableName varchar(50)
 ,@readOnly tinyint=1
 ,@schemaName varchar(50)='dbo'
 ,@debug tinyint=0
AS
BEGIN

if (@readOnly NOT IN (0, 1)) return 10

declare @error int, @rowsCount int, @now datetime2

select 
  @tableName=ltrim(rtrim(@tableName))
 ,@schemaName=ltrim(rtrim(@schemaName)) 
 ,@tablename=t.name
 from sys.schemas s inner join sys.tables t 
   on s.schema_id=t.schema_id 
 where s.name=@schemaName 
 and t.name=@tableName

select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @now=SYSDATETIME()

if (@error<>0) RETURN 20

if (@rowsCount<>1) RETURN 30

declare @createCheckConstraint varchar(8000), 
        @dropCheckConstraint varchar(8000), 
        @createDeleteTrigger varchar(8000), 
        @dropDeleteTrigger varchar(8000)

if (@readOnly=1)
 begin
  select 
   @createCheckConstraint='IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
   +''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'
   +''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)+
   +'ALTER TABLE ['+@schemaName+'].['+@tableName+']  WITH NOCHECK ADD  CONSTRAINT [CK_'+@tableName+'_ReadOnly] CHECK  (1=0)'
   ,@createDeleteTrigger='IF  NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
   +'EXEC dbo.sp_executesql @statement =N'+
   +''''+'CREATE TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete] ON '+'['+@schemaName+'].['+@tableName+']'+char(13)
   +'INSTEAD OF DELETE'+char(13)
   +'AS'+char(13)
   +'BEGIN'+char(13)
   +'RAISERROR( '+''''+''''+'Deletion of table '+@tableName+' not allowed.'+''''+''''+', 16, 1 )'+char(13)
   +'ROLLBACK TRANSACTION'+char(13)
   +'END'+''''

  if (@debug=1)
   begin
   
     print convert(varchar, @now, 126)+ ' @createCheckConstraint='+@createCheckConstraint+' @createDeleteTrigger='+@createDeleteTrigger
   end

  exec (@createCheckConstraint)

  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to create check constraint. will return.'
    return 110
   end

  exec (@createDeleteTrigger) 

  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to create trigger for delete. will return.'
    return 120
   end

 end
else
 begin
  select 
    @dropCheckConstraint='IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
    +''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)
    +'ALTER TABLE ['+@schemaName+'].['+@tableName+'] DROP CONSTRAINT [CK_'+@tableName+'_ReadOnly]'
    ,@dropDeleteTrigger='IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
    +'DROP TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'  
  
  if (@debug=1)
   begin
    print convert(varchar, @now, 126)+ ' @dropCheckConstraint='+@dropCheckConstraint+' @dropDeleteTrigger='+@dropDeleteTrigger
   end

  exec (@dropCheckConstraint)
 
  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to drop check constraint. will return.'
    return 130
   end

  exec (@dropDeleteTrigger)
 
  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to drop trigger for delete. will return.'
    return 140
   end
 end

return 0

END

 

参数说明：

@tableName varchar(50)    --表名
@readOnly tinyint=1       --1表示启用只读，0表示取消只读属性
@schemaName varchar(50)='dbo'   --Schema
@debug tinyint=0                --使用动态查询，方便我们能够看到将要执行什么T-SQL字符串

 

测试效果：

有如下表PERSON及数据：

 

对表PERSON设置只读属性（EXEC usp_Util_SetTableReadOnly 'PERSON', 1），然后执行相关Insert/Update/Delete操作，你将得到错误提示：

 

 

如果要取消只读属性，只需执行如下命令：

EXEC usp_Util_SetTableReadOnly 'PERSON', 0