SQLServerCentral Article

Enable/Disable a table to be read only on the fly

,

It is sometimes very important and critical to protect the content of certain tables. One way to enable this is to make the table read only. However, a read only table is not very useful if its content can never be changed. It may make sense for us to allow the data of a table to be modified only in a controlled and limited way such as using stored procedures.

It would be important and useful if we could switch the read only property of a table at any time. Unfortunately SQL Server does not provide us with a T-SQL statement such as 

ALTER TABLE [schemaName].[tableName] READ ONLY ON|OFF

to switch the read only propery of a table.

There are lot of discussions online available on how to make a table read only in SQL Server. In general, we have the following few options

  1. Create trigger for Insert/Update/Delete
  2. Create check constraint to disable Insert/Update and create trigger to disable Delete
  3. Make the database where the table is in read only
  4. Create a table on a read only file group
  5. Deny table Insert/Update/Delete permission to specified user(s)

While it is possible to use any of these approaches, bear in mind that there are some important limitations and drawbacks in some cases. For example

  • there may be performance issues if you use a trigger to disable Insert/Update for tables with large amounts of data
  • you cannot remove DML permissions for the dbo role and yourself.
  • making a whole database read only may be an overkill
  • you can still add/drop column(s) even if the table is located on a read only file group

In the article, we will use method 2 mentioned above to make a table read only. The procedure usp_Util_SetTableReadOnly makes it easy for us to set read only property of any table to be on or off at any time.

To disable Insert/Update, we add a constraint 1=0 to the table. Please note that since the constraint 1=0 always evaluates to false so we have to use "NO CHECK" when we add this constraint to a table. This allows us to bypass the checking with existing data from the table. Otherwise the constraint will not be able to be added successfully. The constraint will be checked against with new Insert/Update command and this will fail the operation.

When we issue a delete statement against the table, the constraint is not checked. To disable delete we use an instead of trigger.

Assume we have a [TEST] database, the following is the complete code of the stored procedure usp_Util_SetTableReadOnly:

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

usp_Util_SetTableReadOnly has the following parameters:

  • @tableName varchar(50): is the table name which we want to make read only property on or off
  • @readOnly tinyint=1: if 1 will will make the table read only, if 0 we will remove the read only property
  • @schemaName varchar(50)='dbo': the schema of the table
  • @debug tinyint=0: since we use dynamic query here it is handy to be able to see what T-SQL string will be executed

The key responsibility of the procedure is to construct T-SQL strings to drop/add the constraint and trigger and execute them properly. To make sure we can add/drop the constraint and trigger successfully in all the cases, we check the existence of object before adding/dropping it.

Now let's see how this procedure can help us to control the content of a table at ease. First let's create a table ReadOnlyTest and insert few records:

USE TEST
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReadOnlyTest]') AND type in (N'U'))
 DROP TABLE [dbo].[ReadOnlyTest]
GO
CREATE TABLE [dbo].[ReadOnlyTest](
 [RowID] [int] NOT NULL,
 [RowValue] [varchar](50) NOT NULL,
  CONSTRAINT [PK_ReadOnlyTest] PRIMARY KEY CLUSTERED ([RowID] ASC))
GO
INSERT INTO [ReadOnlyTest](RowID, RowValue) Values(1, 'row1: intial set up')
INSERT INTO [ReadOnlyTest](RowID, RowValue) Values(2, 'row2: intial set up')
INSERT INTO [ReadOnlyTest](RowID, RowValue) Values(3, 'row3: intial set up')
GO
SELECT * FROM [ReadOnlyTest]
Next we protect the content of this table by making this table read only.
EXEC usp_Util_SetTableReadOnly 'ReadOnlyTest', 1

Now if anyone tries to Insert/Update/Delete the table, he/she will get error.

INSERT INTO [ReadOnlyTest](RowID, RowValue) Values(4, 'row4: new data')
GO
UPDATE [ReadOnlyTest] SET RowValue='row2: data updated' WHERE RowID=2
GO
DELETE FROM [ReadOnlyTest] WHERE RowID=3

Imagine the table content needs to be changed later; we just disable the read only property.

EXEC usp_Util_SetTableReadOnly 'ReadOnlyTest', 0

We will now be able to Insert/Update/Delete content of this table again.

INSERT INTO [ReadOnlyTest](RowID, RowValue) Values(4, 'row4: new data')
UPDATE [ReadOnlyTest] SET RowValue='row2: data updated' WHERE RowID=2
DELETE FROM [ReadOnlyTest] WHERE RowID=3
SELECT * FROM [ReadOnlyTest]

Finally we set this table to be read only after the content is fixed.

EXEC usp_Util_SetTableReadOnly 'ReadOnlyTest', 1

In summary, this article provides you with a ready-to-use procedure which can help us to set the read only property of any table on or off on the fly. We can easily protect and modify the content of any table using this stored procedure. I hope you find it useful and enjoy using it.

Reference:

Resources

Rate

4.41 (27)

You rated this post out of 5. Change rating

Share

Share

Rate

4.41 (27)

You rated this post out of 5. Change rating