3

SQL SERVER - Making Table Read Only via FileGroup - SQL Authority with Pinal Dav...

 2 years ago
source link: https://blog.sqlauthority.com/2021/07/08/sql-server-making-table-read-only-via-filegroup/?utm_campaign=sql-server-making-table-read-only-via-filegroup
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

SQL SERVER - Making Table Read Only via FileGroup readonly-800x213

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)


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK