January 21, 2019 by Kenneth Fisher
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.
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.