Sunday, August 26, 2007 10:02 AM tonyrogerson

How to make a table Read Only in SQL Server.

If you have a requirement to make a table truly read only (even to sysadmins, well until they disable what you've added :)) usually people would just write a trigger for INSERT, UPDATE and DELETE and do a ROLLBACK but you could also use a CHECK constraint or can you? What is the best way to make a table read only?

First create the table and put some data in it:

CREATE TABLE mytable (

    somedata    varchar(10) not null

)

 

INSERT mytable ( somedata ) VALUES( '1234' )

Now add a check constraint to make it read only:

ALTER TABLE mytable WITH NOCHECK ADD CONSTRAINT chk_read_only CHECK( 1 = 0 )


We simply make a constraint expression that will always evaluate to false.

Now, when we try and update the table we get a constraint violation:

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with the CHECK constraint "chk_read_only". The conflict occurred in database "master", table "dbo.mytable".

The statement has been terminated.

If we try inserting we get a constraint violation:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_read_only". The conflict occurred in database "master", table "dbo.mytable".

The statement has been terminated.

What about a delete?

(3 row(s) affected)

Ah, oh dear – the check constraint didn’t work, why? Check constraints only validate data coming in via Update or Insert activity (Bulk Insert is another example); but, why validate data that’s getting deleted? You don’t, we aren’t interested in that data anymore.

Let’s look at the trigger:

CREATE TRIGGER mytable_read_only ON mytable

    INSTEAD OF INSERT,

               UPDATE,

               DELETE

AS

BEGIN

    RAISERROR( 'This table is read only.', 16, 1 )

 

    ROLLBACK TRAN

 

END

This effectively makes the table read only; but there is a problem here, namely performance. Let’s make our table a bit bigger:

DROP TABLE mytable

 

CREATE TABLE mytable (

    somedata    char(8000) not null

)

 

SET NOCOUNT ON

 

DECLARE @i int

SET @i = 1

 

WHILE @i <= 50000

BEGIN

    INSERT mytable ( somedata ) VALUES( '1234' )

 

    SET @i = @i + 1

 

END

Add the trigger back.

CREATE TRIGGER mytable_read_only ON mytable

    INSTEAD OF INSERT,

               UPDATE,

               DELETE

AS

BEGIN

    RAISERROR( 'This table is read only.', 16, 1 )

 

    ROLLBACK TRAN

 

END

Try an update; it takes an age!

One very important thing to bear in mind here is that I’ve used an INSTEAD OF trigger, if you had used an AFTER trigger which is what most folks are used to then the DELETE, UPDATE or INSERT would of actually been done – that means all the exclusive locks, transaction log writes will all have been done and then need reapplying when you say ROLLBACK – bad!

Change the trigger so it’s only on DELETE’s.

ALTER TRIGGER mytable_read_only ON mytable

    INSTEAD OF DELETE

AS

BEGIN

    RAISERROR( 'This table is read only.', 16, 1 )

 

    ROLLBACK TRAN

 

END

Re try the UPDATE; it’s substantially quicker, this is because on the first row that breaks the constraint the UPDATE is terminated.

UPDATE mytable SET somedata = 'asasdasd'

That’s it; a method for making your table read only in SQL Server. You could just have well used the SELECT permission or DENY permission; however that doesn’t cover sysadmins or db_owners...

DENY UPDATE ON mytable TO [torver\tonyrogerson]


Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

If you really need a table to be truly read only you could also either a) put it in its own database or b) put it on a file group and mark that read only, here’s how:

USE [master]

GO

ALTER DATABASE [csvtosp] ADD FILEGROUP [READONLYTABLES]

GO

ALTER DATABASE [csvtosp] ADD FILE ( NAME = N'mydb_readonly_tables', FILENAME = N'G:\SQL2005DATA\mydb_readonly_tables.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READONLYTABLES]

GO

 

USE csvtosp

GO

 

DROP TABLE mytable

 

CREATE TABLE mytable (

    somedata    char(8000) not null

) ON READONLYTABLES

go

 

INSERT mytable ( somedata ) VALUES( '1234' )

Go

Now you need to make the File Group read only...

ALTER DATABASE [csvtosp] MODIFY FILEGROUP [READONLYTABLES] READONLY


Msg 5070, Level 16, State 2, Line 1

Database state cannot be changed while other users are using the database 'csvtosp'

The problem is that you need to kick people out of the database while you do it, unfortunately WITH ROLLBACK IMMEDIATE doesn’t work for this option so it’s back to KILL.

When you try and INSERT or UPDATE you get a lovely message now:

Msg 652, Level 16, State 1, Line 1

The index "" for table "dbo.mytable" (RowsetId 72057594038779904) resides on a read-only filegroup, which cannot be modified.

My preference would be making the file group read only because another benefit of that is that SQL Server doesn’t have to issue locks. If you decided on the separate database route you simply create a view in the read/write database back to the read/only one – SELECT <cols> FROM readonlydb.schema.table.

One last thing, if you truly want the data so it cannot be modified then these methods are not the approach to take because the sysadmins can tamper; you need to keep the logs or use a linked server out to another locked down SQL Server that has different admin permissions.

 

Filed under:

Comments

# re: How to make a table Read Only in SQL Server.

Sunday, August 26, 2007 2:28 PM by Uri Dimant

Hi Tony.

Well , another good article. What do you think about creating VIEW and DENY permission on underlaying tables?  Sure , it does not cover sysadmins as you are describing here

create table t1 (col1 int,col2 int,col3 int,col4

                int,col5 int,col6 int,col7 int,

                col8 int,col9 int,col10 int)

insert into t1 values (1,1,11,2,5,88,2,6,85,4)

insert into t1 values (8,10,15,2,8,81,20,6,85,4)

insert into t1 values (9,1,11,2,5,81,2,60,99,77)

select * from v1

CREATE VIEW V1 WITH VIEW_METADATA

AS

SELECT

 col1,

 col2+0 AS col2,

 col3,

 col4+0 AS col4,

 col5,

 col6+0 AS col6,

 col7+0 AS col7,

 col8+0 AS col8,

 col9+0 AS col9,

 col10

FROM T1

update v1 set col2=100 where col2=1

---Update or insert of view or function 'v1' failed because it contains a derived or constant field.

--What is about deletes

delete v1 where col2=10

It does deletion , but I think we can write INSTEAD OF DELETE trigger on the view ??.

Thanks for great article

# re: How to make a table Read Only in SQL Server.

Sunday, August 26, 2007 3:08 PM by tonyrogerson

Nice, hadn't thought about going the view route.

Yep - you can have an INSTEAD OF trigger on a view, it's how I do a form of partitioning.

Thanks for the complement again Uri :)

Tony.

# Interesting Finds: August 26, 2007

Sunday, August 26, 2007 4:37 PM by Jason Haley

# re: How to make a table Read Only in SQL Server.

Monday, August 27, 2007 7:56 AM by MarkC

Interestingly, if the table is in a read-only filegroup you can still add and drop columns

alter table mytable add otherdata varchar(10)

alter table mytable drop column somedata

# re: How to make a table Read Only in SQL Server.

Monday, August 27, 2007 8:10 AM by tonyrogerson

That's because the system tables are actually on the PRIMARY file group which is still read / write.

If you try and add a NOT NULL column it won't let you, so it's just NULL columns you can add.

But, the bad thing you've highlighted is that you can drop a column from a table on a file group that is marked read only - that's not good, actually shows that the data is still kept there though - interesting find and nice example :).

In 2005 you'd use a DDL trigger on the ALTER_TABLE event I guess.

I'll write a seperate blog entry on this.

Thanks Mark - good spot mate.

# Read Only File Group - Tables are not really Read Only in the strict sense.

Monday, August 27, 2007 11:09 AM by Tony Rogerson's ramblings on SQL Server

Are read only file groups really read only? What behaviour do we expect on tables that reside on the

# re: How to make a table Read Only in SQL Server.

Monday, September 3, 2007 9:15 PM by degert

SQL Server will still issue shared-locks on a table that resides on a read-only filegroup (at read committed isolation and above).

The optimization only exists for read-only databases.

# Restricting a data in a table from being modified - Windows 8 Guide | Server 2008 | Sql Servers | Tutorial

Pingback from  Restricting a data in a table from being modified - Windows 8 Guide | Server 2008 | Sql Servers | Tutorial

# Rubicon Blog | How to make a table Read Only in SQL Server?

Wednesday, February 12, 2014 11:54 AM by Rubicon Blog | How to make a table Read Only in SQL Server?

Pingback from  Rubicon Blog | How to make a table Read Only in SQL Server?

# How to build an index hunt for a read-only database table? | Zicherman

Pingback from  How to build an index hunt for a read-only database table? | Zicherman