Read-only tables

5

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.

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.

5 thoughts on “Read-only tables

  1. Brent Ozar says:

    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.

  2. Morganwill says:

    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

  3. Poornima says:

    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😃

  4. […] If you want to hit everyone you can always do the DENY on the public role. Well, everyone but dbo and members of […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,507 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: