SQL Server 2016 - Temporal Table - how to identify the user

Question

Is it possible to get the information about the user/connection that modified data that is in the historical table? I read about the audit scenario where I can use temporal tables and that it's possible to detect who has changed the data. But how can I do that?

Answer 1

EDIT: See my much better answer elsewhere on this page

My solution does not need triggers. I have a computed column in the main table which always contains the logged in user, e.g.

CREATE TABLE dbo.Employees(
    EmployeeID INT NOT NULL,
    FirstName sysname NOT NULL,
    ValidFrom DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    LoggedInUser AS (SUSER_SNAME()),  --<<-- computed column

... etc.

The field LoggedInUser always contains the name of the currently logged in user in every record, and is thus saved into the history table at the time any change was made to any record.

Of course, that's not very useful in the main table, as it doesn't show who made the last change for each record. But in the history table it gets frozen at the point the change was made, which is very useful, (although it records the user at the end of the period, not the start).

Note that as a computed column, LoggedInUser must be nullable, and therefore the corresponding column in the history table must be as well.

Main (current) table:

History table:

Of course in the history table, it records who changed the record from that state, not to that state, i.e. the logged in user at the end of the validity period. It works for deletes as well, but the SQL Server temporal table system does not insert a record in the history table for inserts.

Any ideas about how to improve this would be welcome, e.g. how to record who made the change at the start of each validity period in the history table. I have an idea involving another calculated field in the main table, which uses a UDF to get the user who made the last change in the history table.

Edit: I found a lot of inspiration from @Aaron Bertrand's excellent article here, which uses a trigger.