Mr Anderson Mr Anderson - 5 months ago 14
SQL Question

Disallow update on column for all users, in all circumstaces in SQL Server 2012

Is it possible to disallow updates by all users on a column? Consider the following table:

Create table [MyTable]
(
[Id] int not null identity primary key,
[Description] varchar(30) not null,
[Length] int not null
)


In order to maintain my system's integrity, I need the
[Length]
column to not be allowed to change after a record is inserted.

Here are a couple of options I have explored:


  1. using a trigger:

    create trigger [Trigger_MyTable] on [MyTable]
    for update
    as
    begin
    if UPDATE([Length])
    begin
    RAISERROR('Change not allowed.', 16, 1)
    Rollback Transaction
    end
    end

  2. using
    DENY UPDATE
    :

    DENY UPDATE ON [MyTable]([Length]) TO [User1]



The reason I'm disinclined to use the trigger is when working with tables with triggers in the past I have experienced a significant performance declines when performing update statements. (E.G. updating 20,000 rows in table took 4-5 minutes with
instead of update
trigger, < 1 second after dropping trigger.
For update
might be faster, have not tested.)

The
DENY UPDATE
feature would by handy but is there a way to have it apply to ALL users (including admin/owner users)?

Thanks.

Answer

The most reliable way of preventing accidental updates is the trigger.

Permissions aren't checked for people in the sysadmin role or the database owner so the deny won't work, these people can always disable the trigger though so it doesn't add any more security.

If you are on 2012+ there is one more theoretical possibility.

IDENTITY columns are immutable and cannot be updated so you could replace your existing identity with a sequence, make the length column an IDENTITY and ensure all your inserts SET IDENTITY_INSERT on to insert the explicit values.

This might be confusing to other developers though and still wouldn't prevent an update being simulated by a DELETE...INSERT pair.

Comments