Mr Anderson Mr Anderson - 1 year ago 71
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
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
    if UPDATE([Length])
    RAISERROR('Change not allowed.', 16, 1)
    Rollback Transaction

  2. using

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

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


Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download