SQL SERVER – Making Table Read Only via FileGroup

Earlier I wrote the blog post SQL SERVER – Making Filegroup Read Only, and my regular client of  Comprehensive Database Performance Health Check asked me a follow-up question on the practical use of making the filegroup read only. Well, the primary reason, we make the table read-only is for making table read only.

When you have a table on the filegroup which is read only, the table can’t be modified anymore and it also becomes read only. Here is the complete script for your experiment.

USE [master]
GO
-- Step 1: Add FileGroup
ALTER DATABASE [SQLAuthority] ADD FILEGROUP [ReadOnlyFG]
GO
-- Step 2: Add File to FileGroup
ALTER DATABASE [SQLAuthority] ADD FILE
( NAME = N'ReadOnlyFile', FILENAME = N'D:\data\ReadOnlyFile.ndf')
TO FILEGROUP [ReadOnlyFG]
GO
-- Step 3: Create Table and populate it
USE [SQLAuthority]
GO
CREATE TABLE RegularTable (ID INT)
ON [ReadOnlyFG]
GO
INSERT INTO RegularTable (ID)
VALUES (1), (2), (3)
GO
-- Step 4: Make FileGroup ReadOnly
USE master
GO
ALTER DATABASE [SQLAuthority]
MODIFY FILEGROUP [ReadOnlyFG] READONLY
GO
-- Step 5: Test the ReadOnly Property
USE [SQLAuthority]
GO
INSERT INTO RegularTable (ID)
VALUES (4), (5), (6)
GO
-- Step 6: (Optional) Make FileGroup ReadWrite
USE master
GO
ALTER DATABASE [SQLAuthority]
MODIFY FILEGROUP [ReadOnlyFG] READWRITE
GO

Let me know what you think about it. If it is still not clear, I will be happy to build a video on the same topic so it is easy to understand. You can also make the table read only with the permissions, but personally, I find that method bit more complicated and also that does not prevent you from changing the table.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL FileGroup, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Making Filegroup Read Only
Next Post
SQL SERVER – Quick Look at Suspected Pages

Related Posts

1 Comment. Leave new

  • Ahad Zubair
    July 13, 2021 5:01 pm

    Hi Pinal, Thanks for sharing a handy tip however, I believe this “read-only” provision will apply to all tables on that particular Database at once, right? However, incase I have 50 tables in a DB and I want to apply it on few selective tables (say 30 tables) then I have to create different FILEGROUPS for different tables separately OR Have I will achieve it if FILEGROUP is at Database-Level and not at Tables-Level?

    Reply

Leave a Reply

Menu
Exit mobile version