26 August 2007 10:02
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: SQL Server