Enable/Disable a table to be read only on the fly

  • Comments posted to this topic are about the item Enable/Disable a table to be read only on the fly

  • Wouldn't BCP just bypass this, assuming you don't specify CHECK_CONSTRAINTS,FIRE_TRIGGERS hints?

  • The script has a minor mistake. It does not compile in case sensitive databases.

    Variable @tableName is declared with a capital N, but is referenced as @tablename after.

  • Recently I read -I don't remember the source- that you can disable the clustered index and achieve the same effect, since the clustered index IS the table.

    ALTER INDEX [PK_TargetTable] ON [dbo].[TargetTable] DISABLE

  • Jose - I believe that would also disable reads, not just writes.

  • Yes, this is more like taking the table offline.

  • Couldn't you accomplish the same thing with Roles? Set built-in users to datareader role, even get a little bit paranoid if you'd like and explicitly deny datawriter role.

    We currently use a role based set up for our data warehouse. Our updating occurs from a dedicated machine using special proxy account that has datawriter privileges.

    ~ Without obstacles, you cannot progress ~
    http://sqln.blogspot.com/

  • James_DBA (7/30/2013)


    Couldn't you accomplish the same thing with Roles? Set built-in users to datareader role, even get a little bit paranoid if you'd like and explicitly deny datawriter role.

    We currently use a role based set up for our data warehouse. Our updating occurs from a dedicated machine using special proxy account that has datawriter privileges.

    At least as far as applications you are able to use roles. We have things setup that on the application side the user roles are checked and certain objects may or may not be available depending on the user roles. Has worked for years in numerous systems.

    Not all gray hairs are Dinosaurs!

  • Thank you all for your comments. Looks like lot of people are intrested in "making table readonly". Here are some of my reply to some of the comments so far.

    1) By default bcp and bulk insert will ignore constraint for insert. So this solution won't work by default. You would have to add qualifier to be able to use this solution. More information about the options are avaliable from:

    http://msdn.microsoft.com/en-us/library/ms186247(v=sql.105).aspx

    2) Thank you for pointing out the error with @tableName parameter with case sensitive database.

    3) Disableing the clustered index is not a proper way to make table read only. Not every table has clustered index. Also you would have to rebuild the clustered index if you want to really do some useful things with that table later.

    4) Setting read only table per user role is the perfered way to control data access to a table. However sometimes we want to just flip this flag on the fly after changing data to a table. If someone has full access right to database, he/she will be able to do anything regarding any table. The solution here is more about programatically flip the read only flag easily and prevent accidental insert/update/delete statement from general database users ("human").

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply