Different ways to make a table read only in a SQL Server database
By: Jugal Shah | Updated: 2012-06-15 | Comments (15) | 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.
- Insert, Update, Delete Trigger
- Check Constraint and Delete Trigger
- Make the Database Read Only
- Put the Table in a Read Only File Group
- DENY Object Level Permission
- 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')
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.
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.
ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS]
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]
DROP table tblEvents
create table tblEvents
ALTER DATABASE [MyDB] MODIFY FILEGROUP [READ_ONLY_TBLS] READONLY
Any DML operation against the table will fail with the below error.
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.
- 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 Updated: 2012-06-15
About the author
View all my tips