Make Specific Tables Read Only

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 TestingReadOnly and table Testing 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 TestingReadOnly 

CREATE table Testing

(

  ID int,

  LogEvent varchar(1000)

)

INSERT INTO Testing

VALUES (1, 'Password Changed'), (2, 'User Dropped'), (3, '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 TrgReadOnly_Testing ON Testing

    INSTEAD OF INSERT,

               UPDATE,

               DELETE

AS

BEGIN

    RAISERROR( 'Testing 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_Testing, Line 7

Testing 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_Testing on Testing

Add the Check Constraint using the below script.

ALTER TABLE Testing WITH NOCHECK ADD CONSTRAINT chk_read_only_Testing 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_Testing". The conflict occurred in database "TestingReadOnly", table "dbo.Testing".
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_Testing ON Testing

    INSTEAD OF

               DELETE

AS

BEGIN

    RAISERROR( 'Testing 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 [TestingReadOnly] 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 [TestingReadOnly] ADD FILEGROUP [READ_ONLY_TBLS]
GO
ALTER DATABASE [TestingReadOnly] ADD FILE ( NAME = N' TestingReadOnly _ReadOnly_Tables', FILENAME = N'C:\JSPACE\ TestingReadOnlyReadOnly.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READ_ONLY_TBLS]
GO
DROP table Testing
CREATE TABLE Testing
(
ID int,
LogEvent varchar(1000)
)
ON [READ_ONLY_TBLS]
ALTER DATABASE [TestingReadOnly] 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.Testing" (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 Testing TO Sushil

DENY INSERT, UPDATE, DELETE ON Testing 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 vwTesting

as

select ID, Logevent from Testing

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 ‘vwTesting’ failed because it contains a derived or constant field.

Msg 4426, Level 16, State 1, Line 1

View 'vwTesting' is not updatable because the definition contains a UNION operator.