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

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.

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.

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

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.

Jugal Shah

Biography

Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2013