Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Different ways to make a table read only in a SQL Server database


By:   |   Read Comments (15)   |   Related Tips: More > Database Administration

Problem

In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.

Solution

There may be requirements where we have to make specific tables read only. You can make a specific table in database read only by using one of the below techniques. For these examples, we will use database MyDB and table tblEvents for all of the examples.

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

To setup the examples, execute the below script to create the sample database and table.

create database MyDB
create table tblEvents
(
  id int,
  logEvent varchar(1000)
)
insert into tblEvents
values (1, 'Password Changed'), (2, 'User Dropped'), (3, 'Finance Data Changed')

Insert/Update/Delete Trigger

Please note that I have used an INSTEAD OF trigger.  If you use an AFTER trigger it will actually execute the DELETE, UPDATE or INSERT statement which will require locking, writes to transaction log and a rollback which could impact performance.

CREATE TRIGGER trReadOnly_tblEvents ON tblEvents
    INSTEAD OF INSERT,
               UPDATE,
               DELETE
AS
BEGIN
    RAISERROR( 'tblEvents table is read only.', 16, 1 )
    ROLLBACK TRANSACTION
END

Whenever a user executes an INSERT/UPDATE/DELETE statement, the transaction will fail with the below error.

Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7
tblEvents table is read only.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Using Check Constraint and Delete Trigger

Here we will add a check constraint on the table with the expression 1=0, which will always be false. It will not allow you to do an INSERT or UPDATE on any rows.

Here we will first disable the trigger created in the previous step using the below script.

disable trigger trReadOnly_tblEvents on tblevents

Add the Check Constraint using the below script.

ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )

Whenever you execute an INSERT/UPDATE query, it will fail with the below error.

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "chk_read_only_tblEvent". The conflict occurred in database "MyDB", table "dbo.tblEvents".
The statement has been terminated.

But the check constraint will not prevent a DELETE operation. To stop the DELETE, you will also need to create a DDL trigger as shown below.

CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents
    INSTEAD OF 
               DELETE
AS
BEGIN
    RAISERROR( 'tblEvents table is read only.', 16, 1 )
    ROLLBACK TRANSACTION
END

Make the Database Read Only

You can make the database read only and it will not allow any DDL/DML operations for the entire database. Execute the below query to make the database read only.

USE [master]
GO
ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT
GO

Put the Table in a Read Only File Group

Here we will create the table in a separate filegroup and make the filegroup read only.

USE [master]
GO
ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS]
GO
ALTER DATABASE [MyDB] ADD FILE ( NAME = N'mydb_readonly_tables', FILENAME = N'C:\JSPACE\myDBReadOnly.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READ_ONLY_TBLS]
GO
DROP table tblEvents
create table tblEvents
(
id int,
logEvent varchar(1000)
)
ON [READ_ONLY_TBLS]
ALTER DATABASE [MyDB] MODIFY FILEGROUP [READ_ONLY_TBLS] READONLY

Any DML operation against the table will fail with the below error.

Msg 652, Level 16, State 1, Line 1
The index "" for table "dbo.tblEvents" (RowsetId 72057594038845440) resides on a read-only filegroup ("READ_ONLY_TBLS"), which cannot be modified.

DENY Object Level Permission

You can control user permissions by using DCL commands, however it will not prevent users with elevated permissions (for example System Admin, Database Owner).

DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal
DENY INSERT, UPDATE, DELETE ON tblEvents TO Public

Create a View

Instead of giving access to the table, you can use a view.  The view below would prevent any DML operations on it.

create view vwtblEvents
as
select ID, Logevent from tblEvents
union all
select 0, '0' where 1=0

For this view I have added a UNION.  If you use this approach you will need to make sure there are a matching number of columns that are output for each of the queries.  In this example there are two columns, so I have two output columns for both queries.  Also, you need to make sure the data types match as well.

When a user tries to perform an INSERT/UPDATE/DELETE operation they will get the below errors.

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'vwtblEvents1' failed because it contains a derived or constant field.
Msg 4426, Level 16, State 1, Line 1
View 'vwtblEvents1' is not updatable because the definition contains a UNION operator.
Next Steps
  • If you have a need to make a table read only remember these different techniques.
  • If a table will always be read only, you should just move that to a read only filegroup.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, May 22, 2018 - 6:59:29 AM - sukumar samanta Back To Top

Very useful tips. Good effort. Thanks.


Thursday, February 23, 2017 - 1:48:54 AM - Kavitha Back To Top

Very useful tips. Good effort. Thanks.


Thursday, March 12, 2015 - 12:37:26 PM - ShivaReddy Back To Top

Excellent Boss Thank you


Sunday, January 27, 2013 - 9:29:10 AM - JALINDAR Back To Top

Single Query for DATABASE & TABLES

I have 1 Database & Its Table allready E.g Database -> ABC101 AND TABLE CARS. ,

Now I Want to Create New DATABASE E.g. ABC102 & its  table Cars & CarsModel in single Qurey ? Please give me Solution with Query example.

 


Wednesday, September 26, 2012 - 9:02:53 AM - Jugal Back To Top

You can either use the check constraint on the column or don't include that column in any INSERT statement.


Wednesday, September 26, 2012 - 4:55:09 AM - Senthilnathan Back To Top

How to make a column in a table as Write protected in SQL Server


Saturday, June 23, 2012 - 10:01:37 AM - tharg Back To Top

Very clearly explained with the right amount of detail. Thanks Jugal! 


Monday, June 18, 2012 - 8:00:14 PM - TimothyAWiseman Back To Top

An excellent article, thank you for posting it.

Generally, I find permissions the most elegant way to prevent writing to a specific table.  It involves the least code stored in the server and is generally a clean solution.

As you pointed out, this will not stop a sysadmin or dbo from writing to the table.  (As pointed out on MSDN, permission checks are entirely skipped for the sysadmin or dbo of the target database.)  However, someone with a sysadmin or dbo access could easily temporarily suspend any other measures involved as well so that is generally not much of a concern.


Monday, June 18, 2012 - 5:37:21 AM - Swetha Back To Top

Nice article. Thank you!


Monday, June 18, 2012 - 1:47:05 AM - Prasad Back To Top

Good post!! Jugal, waiting to see more tips from u..


Friday, June 15, 2012 - 12:34:58 PM - Ameena Back To Top

 

Thanks for putting all the options in one place. This is a thoughtful article. If I have to choose from these options, I would choose “Create view” as my first choice and “putting table in a readonly filegroup”  as my second choice. I will explain the reasoning behind my choices. Creating and maintaining trigger is complex management work for this simple task. For example if you restore this database to some other place where you do not have read only restriction to that table then you have to remember to remove or disable the trigger and\or constraint. Putting Database in a read only mode is an over kill in my opinion in production because usually you have many tables versus single table in a database. Lastly Deny object level permission is not Microsoft recommended best practice. Having said it all, I understand that in some special scenario any one of them will be more suitable than the other. So “it depends” why you want a read only table and who will be using it and how it will be used.


Friday, June 15, 2012 - 10:17:38 AM - Jugal Back To Top

thank you all for your comments


Friday, June 15, 2012 - 10:09:03 AM - prasad Back To Top

Very nice article Jugal.. keep posting like this.. I appreciate you ....


Friday, June 15, 2012 - 9:10:18 AM - Armando Prato Back To Top

Great tricks... I wish there were a command like ALTER TABLE....READONLY that could be applied so we didn't have to jump
through so many hoops.


Friday, June 15, 2012 - 8:00:10 AM - dharma Back To Top

This article is really good and helped me to understnd more about SQL server.

 

Thanks  for the nice article.


Learn more about SQL Server tools