SQL SERVER – Making Filegroup Read Only

A filegroup read-only setting prevents writes to objects stored there. A customer asked whether a table could be protected this way.

SELECT name,is_read_only,is_default FROM sys.filegroups;
-- Reviewed secondary filegroup template, not PRIMARY:
-- ALTER DATABASE [YourDatabase] MODIFY FILEGROUP [YourFilegroup] READONLY;
-- Approved reversal:
-- ALTER DATABASE [YourDatabase] MODIFY FILEGROUP [YourFilegroup] READWRITE;

The scope covers the filegroup rather than one table. Other tables, indexes, large objects and partitions there can be affected. PRIMARY can’t be made read-only this way. Check documented requirements for the intended secondary filegroup.

Inspect allocation placement and workload before changing it. A table can have indexes or partitions on different filegroups. The change can wait on locks or disrupt required writes.

Permissions can better express an access-control requirement. The READONLY, READWRITE and SSMS options remain administrative choices. Validate a planned change and document its reversal.

Reference: Filegroup read-only requirements.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

A filegroup restriction is not a single-table permission, it is a storage-level change affecting all relevant allocations.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.