How to make a table Read Only in SQL Server?

Question

I am updating some set of records in that table, after that I need to make this table read only.

So how to make a table Read Only in SQL Server?

Answer 1

A simple alternative that would block update and insert on a specific table but still allowing delete:

ALTER TABLE mytable WITH NOCHECK ADD CONSTRAINT chk_read_only CHECK( 1 = 0 ) 

Be aware: this avoids INSERTs and UPDATEs, but allows DELETEs.

You could also put the table into its own database where the LOGIN has only read-only permissions.

The other main option is to move or recreate the read-only tables into a separate FILEGROUP which is marked as READ_ONLY. Note that the PRIMARY filegroup always has to be read/write: only SECONDARY filegroups can be READ_ONLY.

(This solution is only applicable for on-prem SQL Server installations; you can have manual filegroups in Azure SQL but the process is different and not discussed here).

---

Step 1: Create the new (secondary) FILEGROUP, which will be initially read/write:

USE [master];  ALTER DATABASE MyDatabase ADD FILEGROUP MyReadOnlyFileGroup;      ALTER DATABASE MyDatabase ADD FILE (     NAME       = N'mydb_readonly_tables',     FILENAME   = N'G:\SQL2005DATA\mydb_readonly_tables.ndf', /* .MDF = Primary, .NDF = Secondary */     SIZE       = 3072KB, /* SIZE and FILEGROWTH values shown herre are arbitrary. */     FILEGROWTH = 1024KB ) TO FILEGROUP MyReadOnlyFileGroup; 

Step 2: Move the table(s) to the filegroup or re-CREATE them (and copy data) into the new secondary FILEGROUP:

USE MyDatabase;  -- NOTE: Moving tables between filegroups is non-trivial and too complicated to describe in this answer, but see here for more information:  https://www.mssqltips.com/sqlservertip/5832/move-sql-server-tables-to-different-filegroups/ -- It is often much simpler to re-CREATE the table and INSERT INTO to copy data over instead, for example:  CREATE TABLE myschema.myReadOnlyTable (     somedata varchar(8000) NOT NULL,     etc      int           NOT NULL ) ON MyReadOnlyFileGroup;  GO  SET XACT_ABORT ON;  BEGIN TRANSACTION;  INSERT INTO myschema.myReadOnlyTable ( somedata, etc ) SELECT somedata, etc FROM myschema.myMutableTable;  /* DROP TABLE myschema.myMutableTable; -- Uncomment this if you dare.*/  COMMIT TRANSACTION; 

Step 3: Set the READ_ONLY option on the new FILEGROUP:

USE [master];  ALTER DATABASE MyDatabase MODIFY FILEGROUP MyReadOnlyFileGroup READ_ONLY; 

---

If any connections attempt any DML (UPDATE/INSERT/DELETE/MERGE) or DDL (CREATE,ALTER, DROP) operation on a table in a READ_ONLY FILEGROUP then it will fail with an error (usually Msg 652 or Msg 1924):

Msg 1924, Level 16, State 2, Line 123

Filegroup 'MyReadOnlyFileGroup' is read-only.

Msg 652, Level 16, State 1, Line 123

The index "PK_Index" for table "myschema.myReadOnlyTable" (RowsetId 123) resides on a read-only filegroup ("MyReadOnlyFileGroup"), which cannot be modified.

So in order to make any changes to the design of the table, or the data contained within, you'll need to re-use ALTER DATABASE to remove the READ_ONLY option first (but copying data back to the PRIMARY filegroup won't be necessary).

---

Resources:

• https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups
• https://web.archive.org/web/20150926104916/http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/26/how-to-make-a-table-read-only-in-sql-server.aspx
• https://blog.sqlauthority.com/2015/03/17/sql-server-marking-filegroup-as-readonly-with-sql-server/
• https://www.mssqltips.com/sqlservertip/5832/move-sql-server-tables-to-different-filegroups/