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

By:   |   Comments (20)   |   Related: More > Database Administration


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.


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
    RAISERROR( 'tblEvents table is read only.', 16, 1 )

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
    RAISERROR( 'tblEvents table is read only.', 16, 1 )

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]

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]
DROP table tblEvents
create table tblEvents
id int,
logEvent varchar(1000)

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).


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
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, April 3, 2022 - 3:28:54 AM - Meysam Back To Top (89963)
I have some filegroup and I need to make only one of them read only. How can I do it without downtime and not killing connection? Is it possible?

Sunday, January 30, 2022 - 8:36:11 PM - Scott Back To Top (89713)
Putting tables in a "read only" filegroup "almost" protects the table. Unfortunately, you can still run an "Alter table" statement against the table to add/drop tables. You can even drop columns that contain data... so it's not really "read only".. You can create a trigger to prevent ALTER, but you can only apply it to the entire project, not at the table level.

Seriously, this seems like it should be pretty basic functionality. It's not like SQL was developed last Tuesday.

Tuesday, February 2, 2021 - 12:07:51 PM - bishoe Back To Top (88138)
Very clear explanation Thank you

Tuesday, July 21, 2020 - 5:07:13 PM - Greg Robidoux Back To Top (86172)

Hi Donna,

One option is to create a new filegroup and then move the existing table to the new filegroup.  Check out this article: https://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/

Then you can make the new filegroup read only.  Check out this article: https://blog.sqlauthority.com/2015/03/17/sql-server-marking-filegroup-as-readonly-with-sql-server/

Another thing you can do is make changing an existing filegroup from read_only to read_write, move the table as shown above and then change the filegroup back to read_only.   You can refer to this for the read only and read write options: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-ver15


Friday, July 17, 2020 - 6:55:44 AM - Donna Back To Top (86150)

How would you put an EXISTING table into a read-only file group?

(Your example forgets to tell anyone that those steps will entirely destroy your existing table. Ugh.)

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

Very useful tips. Good effort. Thanks.

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

Very useful tips. Good effort. Thanks.

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

Excellent Boss Thank you

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

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 (19690)

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 (19686)

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 (18185)

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

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

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 (18081)

Nice article. Thank you!

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

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

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


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 (18035)

thank you all for your comments

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

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

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

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 (18031)

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


Thanks  for the nice article.

get free sql tips
agree to terms