Database read only but only few tables writable

Question

I am trying to set the database as read only mode with the following command and it works.

ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT

However, I need to allow only few tables like UserSession, etc to be writable. Is it possible?

We have around 500+ tables in the database and we need to allow only 4 tables to be writable.

Answer 1

You can archive this via moving the writable tables to a seperate Filegroup and making the other filegroup readonly.

Step One - Create a Different Filegroup

ALTER DATABASE TESTDB ADD FILEGROUP Writable_FG;

Step Two - Add a Data File to The New Filegroup

ALTER DATABASE TESTDB ADD FILE (
     NAME = JeanAnn2,
     FILENAME = 'D:\MSSQL\TESTDB_Writable_FG_01.ndf',
     SIZE = 6MB,
     MAXSIZE = 18MB,
     FILEGROWTH = 1
) TO FILEGROUP Writable_FG; 

Step Three - Move the tables that you need to be writable, to the new filegroup

For this you need to recreate the clustered index of the table on the new filegroup.

CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [Writable_FG]

or if your clustered index is unique:

CREATE UNIQUE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [Writable_FG]

Do this for all four tables that you need to be writable.

Step Four - Make the other filegroup readonly

ALTER DATABASE TESTDB MODIFY FILEGROUP [PRIMARY] READ_ONLY;

Here, it is assumed that the other filegroup is the Primary.

Answer 2

My suggestion would be to store the two types of tables in different schemas -- say readable and writable.

When SQL Server introduced schemas in 2005, the idea was that schemas are a unit for security and databases are a unit for backup and recovery:

Microsoft SQL Server 2005 introduced the concept of database object schemas. Schemas are analogous to separate namespaces or containers used to store database objects. Security permissions apply to schemas, making them an important tool for separating and protecting database objects based on access rights.

This would make two different schemas a strong contender for your data architecture.

In other words, set the readable schema to read_only. And put the other tables in the writable schema.

Answer 3

You could deny INSERT,UPDATE,DELETE to public role like:

DENY UPDATE ON tab1 TO public 
DENY INSERT ON tab1 TO public 
DENY DELETE ON tab1 TO public 

After that, regular users (not sysadmins) will get an error when trying to insert into tab1:

INSERT INTO tab1 (id) VALUES (1)

--Msg 229, Level 14, State 5, Line 20
--The INSERT permission was denied on the object 'tab1', database 'test', schema 'dbo'.

If this is something that you're comfortable adding to your database, then you could generate the script to change all tables like:

EXEC sp_msforeachtable '
PRINT ''
    DENY UPDATE ON ? TO public 
    DENY INSERT ON ? TO public 
    DENY DELETE ON ? TO public
''
'

Make sure you're not denying writes to those 4 tables that still need to be written to.

Not sure about your application, but if you dont want to deny permissions on public role, you may look into creating your own role, denying access on that role, and making users part of that role instead.

Hope it makes sense.