WikiDBA

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(50) NULL )
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 INSERT, UPDATE, DELETE
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(50) NULL )
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=0 

• 4) DENY Object Level Permission

We can deny user level permissions as

DENY INSERT, UPDATE, DELETE ON TBLTEST TO AnyUserName

DENY INSERT, UPDATE, DELETE 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