Posts Tagged ‘Creating a trigger to make a table as read only’

Generally in Production environment or in some cases there may be a requirement to make a SQL Server table as a read only. In SQL Server, there are many ways to do this. Some of few techniques are as below,

To demonstrate this, here DB name is VIRENDRATEST and Table name is TBLTEST

CREATE DATABASE [VIRENDRATEST]
CONTAINMENT NONE
ON PRIMARY
NAME N’VIRENDRATEST’FILENAME = N’D:\TestDataBases\VIRENDRATEST.mdf’ SIZE = 3072KB FILEGROWTH = 1024KB )
LOG ON (NAME=N’VIRENDRATEST_log’,FILENAME=N’D:\TestDataBases\VIRENDRATEST_log.ldf’,SIZE=1024KB FILEGROWTH = 10%)
GO

 And a table TBLTEST as

CREATE TABLE TBLTEST
    ID int NULL,
Name varchar(50NULL )
ON 
[PRIMARY]
GO

To making table as a read only, following techniques may be used

  1. Trigger – Insert, Update, Delete
  2. Put the Table in a Read Only File Group
  3. Create a View
  4. DENY Object Level Permission
  5. Make the Database as Read Only

 

1)  Trigger – Insert, Update, Delete 

Insert, Update and Delete trigger may be implemented on table as

CREATE TRIGGER TrgReadOnly_TblTest ON TblTest
INSTEAD OF INSERTUPDATEDELETE
AS
BEGIN
RAISERROR(‘Table is Read Only’, 16, 1 )
ROLLBACK TRANSACTION
END

 

2)  Put the Table in a Read Only File Group

We can put Table on a Read only file group.

USE [Master]
GO

ALTER DATABASE [VIRENDRATEST] ADD FILEGROUP [READONLYTABLES]
GO

ALTER DATABASE [VIRENDRATEST] ADD FILE NAME N’READONLYTABLES’FILENAME = N’D:\TestDataBases\VIRENDRATEST.ndf’ ,
SIZE= 2048KB FILEGROWTH = 1024KB ) TO 
FILEGROUP [READONLYTABLES]
GO

CREATE TABLE TBLTEST
ID int NULL,
  Name 
varchar(50NULL )
ON 
[READONLYTABLES]
GO

ALTER DATABASE [VIRENDRATEST] MODIFY FILEGROUP [READONLYTABLES] READONLY

3) Create a View
The easiest solution for making a table as read only is VIEWs. As per below views creations it will prevent DML operation on table.

Create View VwTBLTEST as
select 
ID, Name from TBLTEST
union all
select 0
‘0’ where 1=

  • 4) DENY Object Level Permission

We can deny user level permissions as

DENY INSERTUPDATEDELETE ON TBLTEST TO AnyUserName

DENY INSERTUPDATEDELETE ON TBLTEST TO Public

5) Make the Database as Read Only

Making a Database as a Read Only, it will not allow to anyone to perform any DDL or DML operation on Database. (be sure where is it recommended as per your work environment)

USE [Master]
GO

ALTER DATABASE [VIRENDRATEST] SET READ_ONLY WITH NO_WAIT
GO