SQL Server Prevent Update on Column (datetime2 column value set by database on insert)

Question

I have a table Values with 3 columns:

CREATE TABLE [dbo].[Values]
(
    [Id] [uniqueidentifier] NOT NULL,
    [Value] [nvarchar](150) NOT NULL,
    [CreatedOnUtc] [datatime2](7) NOT NULL
)

I want SQL Server to set the value of CreatedOnUtc to UTC-Now whenever a new entry is created, and not allow an external command to set this value.

Is this possible?

You can define a `DEFAULT` value, yes. As for stopping people, you would either need to ensure that users can only interact (with `INSERT`/`UDPATE` statements) via stored procedures, or `DENY` them the ability to update the column. Of course, a `sysadmin` would still get around this, but people shouldn't be using a `sysadmin` account for "day to day" access. — Thom A, Aug 13 '20 at 12:24

Gordon Linoff · Answer 1 · 2020-08-14T10:33:20.380

This is sort of two questions. For the first:

CREATE TABLE [dbo].[Values] (
    [Id] [uniqueidentifier] NOT NULL,
    [Value] [nvarchar](150) NOT NULL,
    [CreatedOnUtc] [datetime2](7) NOT NULL DEFAULT SYSUTCDATETIME() 
);

The canonical way to prevent changes to the column is to use a trigger that prevents the value from being updated or inserted.

Note that Values is a really bad name for a table because it is a SQL keyword and SQL Server reserved word. Choose identifiers that do not need to be escaped.

There are other ways. For instance, you could turn off DML access to the table. Then create a view without CreatedOnUtc and only allow inserts and updates through the view.

You are right about the scope - I'd update the title – Roi Shabtai Aug 14 '20 at 05:57 — Roi Shabtai, Aug 14 '20 at 05:57

SQL Server Prevent Update on Column (datetime2 column value set by database on insert)

1 Answers1