sql - 如何在 SQL Server 中将表设置为只读？

Question

我正在更新该表中的一些记录，之后我需要将该表设置为只读。

那么如何在 SQL Server 中将表设置为只读？

Answer 1

一个简单的替代方案，可以阻止特定表上的更新和插入，但仍允许删除:

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

请注意:这可以避免插入和更新，但允许删除。

您还可以将该表放入其自己的数据库中，其中 LOGIN 仅具有只读权限。

另一个主要选项是将只读表移动或重新创建到一个单独的FILEGROUP中，该表被标记为READ_ONLY。请注意，PRIMARY 文件组始终必须是读/写的:只有 SECONDARY 文件组可以是READ_ONLY。

(此解决方案仅适用于本地 SQL Server 安装；您可以在 Azure SQL 中拥有手动文件组，但过程有所不同，此处不予讨论)。

<小时/>

第 1 步:创建新的(辅助)FILEGROUP，该文件最初将被读/写:

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;

第 2 步:将表移动到文件组或重新创建它们(并将数据复制)到新的辅助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;

第 3 步:在新的 FILEGROUP 上设置 READ_ONLY 选项:

USE [master];

ALTER DATABASE MyDatabase MODIFY FILEGROUP MyReadOnlyFileGroup READ_ONLY;

<小时/>

如果任何连接尝试任何 DML (UPDATE/INSERT/DELETE/MERGE) 或 DDL (CREATE、ALTER、DROP) 对 READ_ONLY FILEGROUP 中的表进行操作，然后将失败并出现错误(通常为 Msg 652 或 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.

因此，为了对表的设计或其中包含的数据进行任何更改，您需要重新使用 ALTER DATABASE 删除 READ_ONLY 首先选择选项(但无需将数据复制回 PRIMARY 文件组)。

<小时/>

资源:

• 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/