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
create trigger [Trigger_MyTable] on [MyTable]
RAISERROR('Change not allowed.', 16, 1)
DENY UPDATE ON [MyTable]([Length]) TO [User1]
instead of update
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