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|OFFto 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
- Create trigger for Insert/Update/Delete
- Create check constraint to disable Insert/Update and create trigger to disable Delete
- Make the database where the table is in read only
- Create a table on a read only file group
- 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
ENDusp_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]
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:
- 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/
