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


Advertisements
Comments
  1. Andy Hayes says:

    Nice post. It’s a shame there is not a simple read only setting for a table. Something like ALTER TABLE TableName SET READ_ONLY would make a DBA’s life a little easier 🙂

    Like

  2. Tony says:

    As it see it, not only is there a need to set a table to be read-only but also to prevent changes to a table’s structure (but I guess that in most companies there are restrictions in place for changing production objects) but to be able to prevent a table from being deleted – like some staging tables for which there is no need to add relationships but necessary for them to remain present.
    In fact, these are all possible with features like database triggers or to some degree with capabilities like with schema_binding.

    Tony

    Like

  3. Neeraj says:

    Nice info, Gud one

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s