http://www.sqlservercentral.com/articles/read+only/100618/
USE TEST
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Util_SetTableReadOnly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
GO
-- =================================================================================================
--
-- Author Date Description
-- ================================================================================================
-- Steven Rao (StevenZRAO@gmail.com) 2013-07-13 original draft
-- ================================================================================================
-- PURPOSE: Enable/Disable read only property to any give table
-- =================================================================================================
-- Reference:
-- http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/
-- http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/26/how-to-make-a-table-read-only-in-sql-server.aspx
-- http://www.sqlservercentral.com/articles/Advanced+Querying/readonlytables/2517/
-- =================================================================================================
CREATE 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
--
-- verify schema and table name, also trim them if needed
-- warning: don't swtich the order of assignment here!
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
