Setting a SQL Server Table to Read-Only

There are situations when you need to set a database to read-only, but what do you do when you need a single table to be read-only? It’s not as straightforward as setting the entire database read-only, but it can still be done.

Let’s take a look at the steps necessary to set both a database and a single table to read-only.

Read-Only Database

Setting an entire database to read-only is straightforward. This can be accomplished by running the following script for your database:

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

This sets the ChadTest database to read-only. Once set, you can SELECT from the database just as you would if it were in read/write status. But any changes, such as the UPDATE below, will throw an error message:

If you need to revert back and continue making changes to a database, you can set the database to READ_WRITE by running:

USE [master]
GO
ALTER DATABASE [ChadTest] SET READ_WRITE WITH NO_WAIT
GO

Read-Only Table

There’s no SQL Server setting to set a specific table to read-only like there is with a database. One option for a table is to use a trigger to prevent changes, effectively making it read-only. For example, if I wanted to make only the Comic table in my database read-only instead of the entire ChadTest database, I could add this trigger:

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

Now, if I try to make an update, insert, or deletion, I’ll get the following:

If changes are needed with the table, you can drop the trigger. If you know you’ll want to lock the table down again, you can disable the trigger, make changes, and re-enable the trigger when you’re ready:

DISABLE TRIGGER Comic_Read_Only_Trigger ON Comic;  
GO  
ENABLE TRIGGER  Comic_Read_Only_Trigger ON Comic;  
GO

Easily Triggered

Creating a read-only table in SQL Server requires a bit more effort compared to setting an entire database to read-only. Using a trigger is the option I’m most familiar with when it comes to read-only tables. It’s quick to implement and has served me well so far. If you run into this type of situation, I recommend giving it a shot.

Thanks for reading!