如何在 SQL Server 中使表只读？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2529839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a table Read Only in SQL Server?
提问by Jeyavel
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?
那么如何在 SQL Server 中使表只读呢？
采纳答案by gbn
- Trigger with rollback trans
- Read only filegroup
- 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.
第 3 条可能是最佳实践。例如，如果您的连接是 db_owner，那么触发器可以禁用触发器或无论如何将表移动到不同的文件组。
回答by Leniel Maccaferri
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.
请注意：这避免了 INSERT 和 UPDATE，但允许 DELETE。
If you really need a table to be truly read only you could also either:
如果你真的需要一个真正只读的表，你也可以：
a) put it in its own database or
b) put it on a file group and mark that read only, here's how:
a) 将其放入自己的数据库中或
b) 将其放入文件组并标记为只读，方法如下：
USE [master]
GO
ALTER DATABASE [csvtosp] ADD FILEGROUP [READONLYTABLES]
GO
ALTER DATABASE [csvtosp] ADD FILE ( NAME = N'mydb_readonly_tables', FILENAME = N'G:\SQL2005DATA\mydb_readonly_tables.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READONLYTABLES]
GO
USE csvtosp
GO
DROP TABLE mytable
CREATE TABLE mytable (
somedata char(8000) not null
) ON READONLYTABLES
GO
For more details on this subject, go here:
有关此主题的更多详细信息，请访问此处：
回答by Duniyadnd
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.
如果您希望它仅对一般公众只读，但仍希望能够在以后编辑该表，您可能需要考虑为该数据库创建多个用户并向该数据库授予不同的权限 - 理想情况下，您应该无论如何都要这样做，并且不允许公众访问更改表、截断等。
