SQL: "Write Protect" row of data possible?

Question

May be a stupid questions but can I protect a row of data in a SQL Server database from being deleted or updated without setting user permissions?

This is for a default row of data that can be referenced for its default values?

Thanks

Answer 1

You could make a trigger that raises an error if that row is updated or deleted.

Answer 2

Let's say that your MyTable is on Primary.

Put the first row into a new table MyTableReadOnly, move that table to it's own File Group, and make the file group read only.

Remove the first row from the MyTable

Now create a view that

SELECT Columns From MyTableNew
UNION
SELECT Columns From MyTable

Access everything through the view. If you want to update or delete from the view, you can do that on MyTable and ignore anything for MyTableNew. If you want to work with the view, you can use INSTEAD-OF triggers.

Answer 3

• Create a second table that has rows with the same unique IDs as the rows you are trying to protect.

• Permission that SECOND table as you wish

• Add a trigger on the first table which will delete/update both tables if a matching row exists in the second table.

This way, unless you have a perm on the second table, you will not be able to modify the "linked" rows as the trigger will terminate due to permissions violation on the second table

NOTE: The contrast of this method with the other main methods (using VIEWs) is that it allows easy maintenance of the set of "fixed" rows unlike the VIEW approach and avoids various performance problems usually associated with views.

Answer 4

One possible approach that I once used is described in my blog post:

"Suppose that you need to enforce the following business rule: contracts cannot be changed after you have started working on them (let us assume that that particular business operates in the perfect world). You can use a ROWVERSION column, a persisted computed one, and a foreign key constraint to implement this rule - Using ROWVERSION to enforce business rules

Answer 5

Do this by relational integrity - do NOT use triggers as they're always a real pain to maintain afterwards (they have their place, just not here). Relation integrity will do everything you need.

Using relational integrity can be quite elegent, but what you need to do is slightly counter-intuitive so easily missed.

Create your main table table, tblMain, with a numeric primary key. For simplicity I tested this with a table with one column, intID, and I populated it with values 0,1 and 2.

Next create a second table, tblGuard, with a similar numeric primary key. I added one row into this table, value 1.

Now the reverse logic bit. Create a foreign key on the tblGuard table that reference the tblMain table

ALTER TABLE [dbo].[tblGuard] ADD 
    CONSTRAINT [FK_tblGuard_tblMain] FOREIGN KEY 
    (
     [intID]
    ) REFERENCES [dbo].[tblMain] (
     [intID]
    )

The constraint will ensure that the row with intID value 1 cannot be deleted from the tblMain table because the tblGuard table referential integrity requires that the value 1 exists in tblMain. This works with deletes and truncates.

Answer 6

I say do this "programmatically". For example let the row with id 1 always be the default row, then add to all UPDATE or DELETE queries "WHERE id != 1" or do the equivalent in whatever language you are using to write your logic (PHP, C, VB, etc.)