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

Question

Post reply

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

stevenzrao

SSC Enthusiast

Points: 131
More actions
July 29, 2013 at 10:42 pm

#280727

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

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

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

RG-351877 SSC Rookie Points: 42 More actions · Answer 1

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

calmeidajr@gmail.com SSC Rookie Points: 25 More actions · Answer 2

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.

José Elizondo Sánchez Valued Member Points: 65 More actions · Answer 3

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

Dennis Dupont SSC Rookie Points: 35 More actions · Answer 4

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

José Elizondo Sánchez Valued Member Points: 65 More actions · Answer 5

José Elizondo Sánchez

Valued Member

Points: 65

July 30, 2013 at 9:02 am

#1637028

Yes, this is more like taking the table offline.

James_DBA Default port Points: 1422 More actions · Answer 6

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/

Miles Neale SSChampion Points: 13147 More actions · Answer 7

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!

stevenzrao SSC Enthusiast Points: 131 More actions · Answer 8

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").