SQL SERVER – Making Filegroup Read Only

Is it possible to make any table read only? Well, not directly but you can for sure do that by making filegroup read only. I was asked this in my recent  Comprehensive Database Performance Health Check. Let us see the solution.

Here is the script to make any filegroup a read only filegroup.

ALTER DATABASE DBName MODIFY 
FILEGROUP Read_Only READONLY

Once the filegroup is read only, you can read the data from the table on the same filegroup but can’t insert, update, or delete. I hope you like this little trick.

If you want to bring back the filegroup to readwrite, you can execute the following command.

ALTER DATABASE DBName MODIFY 
FILEGROUP Read_Only READWRITE

You can also do this from the database properties and filegroup tab.

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)

Exit mobile version