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

Question

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

sqlsql-serversql-server-2005

24

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

那么如何在SQL Server中使表只读？

- Jeyavel

1

这里有几个很好的答案。我的问题是：谁必须使用只读表？系统管理员总是有权力做任何事情，尽管可能需要他们花费时间和精力。 - Philip Kelley

2

除系统管理员用户外 - Jeyavel

3个回答

10

使用回滚事务的触发器
只读文件组
不要授予插入/更新/删除权限

第三点可能是最佳实践。例如，如果您的连接是db_owner，则可以禁用触发器或将表移动到不同的文件组。

- gbn

3

如果您希望将其设置为对普通公众只读，但仍希望能够在以后编辑表格，则可能需要考虑为数据库创建多个用户，并授予该数据库不同的权限-最好是一直这样做，并且不允许普通公众访问更改表格、截断等操作。

- Duniyadnd

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Leniel Maccaferri · Accepted Answer

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

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

注意：这个方法可以避免 INSERT 和 UPDATE，但是会允许 DELETE。

你也可以将表放在自己的数据库中，然后将 LOGIN 的权限设置为只读。

另一个主要选择是将只读表移动或重新创建到单独被标记为 READ_ONLY 的 FILEGROUP 中。请注意，PRIMARY 文件组总是需要读写权限，只有 SECONDARY 文件组可以是只读的。

（此解决方案仅适用于本地 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;

步骤二：将表移动到文件组或重新创建它们（并将数据复制）到新的辅助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;

步骤三：在新的FILEGROUP上设置READ_ONLY选项:

USE [master];

ALTER DATABASE MyDatabase MODIFY FILEGROUP MyReadOnlyFileGroup READ_ONLY;

如果任何连接尝试在一个READ_ONLY FILEGROUP中的表上执行任何DML(UPDATE/INSERT/DELETE/MERGE)或DDL(CREATE,ALTER, DROP)操作，则会失败并显示错误(通常是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文件组)。

资源: