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中读取?
3 个解决方案
#1
29
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.
注意:这避免了插入和更新，但允许删除。
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:
关于这个主题的更多细节，请到这里:
How to make a table Read Only in SQL Server
如何使表仅在SQL Server中读取
#2
8
- Trigger with rollback trans
- 触发器与回滚转
- Read only filegroup
- 只读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.
第三点可能是最佳实践。例如，如果您的连接是db_owner，那么可以禁用触发器或将表移到另一个文件组。
#3
3
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.
如果你想要它作为只读公众,但仍然希望能够编辑表在一段时间以后,你可能想要考虑创建数据库的多个用户,授予不同的权限,数据库——理想情况下你应该这样做,不允许公众访问alter table,截断等等。
#1
29
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.
注意:这避免了插入和更新，但允许删除。
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:
关于这个主题的更多细节，请到这里:
How to make a table Read Only in SQL Server
如何使表仅在SQL Server中读取
#2
8
- Trigger with rollback trans
- 触发器与回滚转
- Read only filegroup
- 只读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.
第三点可能是最佳实践。例如，如果您的连接是db_owner，那么可以禁用触发器或将表移到另一个文件组。
#3
3
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.
如果你想要它作为只读公众,但仍然希望能够编辑表在一段时间以后,你可能想要考虑创建数据库的多个用户,授予不同的权限,数据库——理想情况下你应该这样做,不允许公众访问alter table,截断等等。