Immutable SQL columns

Question

Is it possible to mark a column immutable in MSSQL?

Seems like it would be a useful DDL feature; once a value is set in a row ('row' being defined as a specific relation of values to a primary key), it could not be changed without deletion of the row.

Obviously (like most things) this is more than doable in the application layer, but half the fun of SQL DDL is error-checking your application code.

Answer 1

If the user doing the DML is not the owner of the objects and not "db_owner" in the database itself, you can just grant "insert" privilege, but not update privilege for that table:

Assuming a table with id, col1, col2

grant insert, select, delete on the_table to the_user;
grant update (id, col2) on the_table to the_user;

With these grants the_user can insert rows and supply values for all three columns. He can also update the id and the col2 column, but not the col1 column.

The db_owner (and possibly the creator/owner of the table) can always update all columns. I don't know if there is a way to revoke that privilege from those rolws.