Read-only tables
5January 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.
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.
From the Bad Idea Jeans Outlet Store: triggers on insert/update/delete, and roll ’em back or log ’em to a separate table. You’re so not welcome.
LOL Yea well, the way most people write triggers I can only imagine how badly things would go.
Thank you so much, I’ve already found many helpful tips here.
As I’m desperately looking for some solutions, which i can also use for my company Indiaaccess and Hostasp Servers
Thanks for your info
I can think of few places where it will be helpful like- archived data in a history database. It needs to stay for documentation but no changes will be done. Some lookup tables etc.
read only file group is interesting. I can perplex some folks with that😃
[…] If you want to hit everyone you can always do the DENY on the public role. Well, everyone but dbo and members of […]