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

Question

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 1

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.