Read-only tables

Kenneth Fisher, 2019-02-05 (first published: 2019-01-21)

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads