SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Read-only tables

Sometimes you need a table that is read only. You don’t want anyone to be able to write to it for various reasons. It might be historical data that shouldn’t/can’t be changed, it might be lookup information that shouldn’t be changed, etc. There are a couple of ways to handle this.

Deny Write

You can deny write access to the public role. This is probably the easier way to go but on the downside, it doesn’t stop members of the sysadmin server role, or the dbo (database owner) from making changes. It will stop members of the db_owner role though. First of all, it’s easy to turn off temporarily by changing the permissions, second, it’s got more flexibility. You can deny update/delete permissions but still allow insert. Or instead of using the public role you could use some other role that doesn’t include a few admins and let them add/modify data as needed. (It’s not really read-only at that point but still useful.)

Read only filegroup

Databases contain filegroups that contain files that are where the tables in a database are stored. You can mark these filegroups as READ_ONLY. Comparing this to permissions: It’s a bit harder to set up. You have to put the table into a filegroup and then alter the database (not hard but still more restrictive than changing permissions) to mark the filegroup as READ_ONLY. It’s also less flexible. It’s READ ONLY. No one, and I mean no one, not even sysadmins can modify the table without marking the filegroup back to READ_WRITE. That said, it’s more, well, read-only. Like I said, no one will actually be able to modify the data. This could be important for a historical legal table for example. On the other hand, if you want to add a new read-only table to the filegroup you have to mark it as READ_WRITE, add the table, then mark it back to READ_ONLY. Oh, and I should point out it does have some effect on database recovery (you can recover READ_ONLY separately from the READ_WRITE ones).

Read only database

Really this is the same as the filegroup but you are marking the entire database as read-only. Meaning that if you want to grant new people access you either have to mark it as read-write temporarily or use AD groups for your security, to begin with.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...