This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an option to create a Read Only table in SQL Server.
I also shared an article on how to create a read only database in SQL Server?
SQL Server: Script to make Database Read Only and Read Write
If you thinking to give a SELECT permission on the table, you have to give SELECT permission to particular group/user.
But, here I am talking about complete Read Only Table, which has by default only SELECT permission for all the users/groups.
How to create a Read Only Table?
Add one file group to database:
1 |
ALTER DATABASE my_database ADD FILEGROUP [Read_Only] |
Create a new data file:
1 |
ALTER DATABASE my_database ADD FILE ( NAME = N'data_file_name', FILENAME = N'D:\data_file_path.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Read_Only] |
Create a table under newly created data group:
1 |
CREATE TABLE test_readonly (id INT) ON [Read_Only] |
Load/Insert your data:
1 |
INSERT INTO test_readonly VALUES (1),(2),(3),(4) |
Now, set READONLY to newly created file group:
1 |
ALTER DATABASE my_database MODIFY FILEGROUP [Read_Only] READONLY |
Try to insert same records:
Now, you can’t insert/update/delete the data because a table is Read Only now.
1 2 3 4 |
INSERT INTO test_readonly VALUES (1),(2),(3),(4) Msg 652, Level 16, State 1, Line 8 The index "" for table "dbo.test_readonly" (RowsetId 72057594042187776) resides on a read-only filegroup ("Read_Only"), which cannot be modified. |
Leave a Reply