Making a Table 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 = NONEON 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 TblTestINSTEAD OF INSERT, UPDATE, DELETEASBEGINRAISERROR(‘Table is Read Only’, 16, 1 )ROLLBACK TRANSACTIONEND

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

Splitting comma separated column’s values in rows

I have seen many Developer/DBA uses user defined function or stored procedure to split a comma separated ( or any delimiter to separate values like |,;-.& or any character/special character) column into rows using various coding logic like while/for loop or using cursor etc.

Let see an example, below is a sample data,

And requirement is to split the every SkillSet column values in a single row according EMPID and EMPNAME like

To get above desired result without using any Function, Stored Procedure or any loop, simple flat SQL select statement will be like this,

SELECT EmpID,EmpName,Split.Data.value(‘.’, ‘VARCHAR(100)’) AS String
FROM (Select EmpID,EmpName,CAST(‘<M>’ + REPLACE(SkillSet,‘,’, ‘</M><M>’)+ ‘</M>’ AS XML) AS StringFROM VirendraTest) AS Data CROSS APPLY String.nodes (‘/M’) AS Split(Data)

If you want to do RnD, below is the sample code for same.

–Step 1 ) Create a Sample table named as VirendraTest

CreateTable VirendraTest(EmpID Char(5),EmpName Varchar(30),SkillSet nvarchar(100))
go

–Step 2 ) Insert few sample data in above created table i.e. VirendraTest
Insert into VirendraTest
values(‘E001’,‘Virendra Yaduvanshi’,‘MS-SQL Server,C,C++,C#,.Net’),
(‘E002’,‘Manish Raj’,‘Account,Cash Management,Admin’)
      (‘E003’,‘Sanjay Singh’,‘.Net,VB6,Oracle’),
      (‘E004’,‘Shajia Khan’,‘Coldfusion,Delphi’),
      (‘E005’,‘Vikash Rai’,‘Php,Mongo,Cloud’),
      (‘E006’,‘Sandeep Arora’,‘Network,C,C++’),
      (‘E007’,‘Manpreet Kaur’,‘Java,Android,Mobile Devlopment’)
–Step 3 ) Check Table contents

Select * from VirendraTest

–Step 4 ) Split comma seperated column SKILLSET in rows using below query

SELECT EmpID,EmpName,Split.Data.value(‘.’, ‘VARCHAR(100)’) AS String
FROM (Select EmpID,EmpName,CAST(‘<M>’ + REPLACE(SkillSet,‘,’, ‘</M><M>’)+ ‘</M>’ AS XML) AS StringFROM VirendraTest) AS Data CROSS APPLY String.nodes (‘/M’) AS Split(Data)