How to make a table Read Only in SQL Server?

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?

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://learn.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/

1. Trigger with rollback trans
2. Read only filegroup
3. Don't grant insert/update/delete permissions

Number 3 is probably best practice. For example, if your connection is db_owner for example then the trigger can be disabled the trigger or move the table to a different filegroup anyway.

If you want it as read only to the general public, but still want to be able to edit the table at a later date, you may want to consider creating multiple users for the database and granting different permissions to that database - ideally you should be doing this anyway and not allow the general public access to alter table, truncate etc.