Ytukyg

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?

A seemingly watertight auditing solution, which gives the name of the logged-in user who made each change (and a great improvement on my previous answer on this page):

SELECT 

  e.EmployeeID, e.FirstName, e.Score, 

  COALESCE (eh.LoggedInUser, o.CreatedBy, e.CreatedBy) AS CreatedOrModifiedBy, 

  e.ValidFromUTC, e.ValidToUTC



FROM dbo.Employees FOR SYSTEM_TIME ALL AS e

  LEFT JOIN dbo.EmployeeHistory AS eh    -- history table

    ON e.EmployeeID = eh.EmployeeID AND e.ValidFromUTC = eh.ValidToUTC



OUTER APPLY

  (SELECT TOP 1 CreatedBy 

   FROM dbo.EmployeeHistory 

   WHERE EmployeeID = e.EmployeeID 

   ORDER BY ValidFromUTC ASC) AS o     -- oldest history record



WHERE e.EmployeeID = 1

ORDER BY e.ValidFromUTC

• Does not use triggers or user defined functions


• Requires small changes to the table


• 
  NB: Note that SQL Server always uses UTC, not local time, for time stamps in temporal tables.

Explanation:

Two fields are added to the main and history tables:

• To record the name of the user who created the record - a normal SQL default:
  
  CreatedBy NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME())
  


• To record the name of the current logged in user at any time. A computed column:
  
  LoggedInUser AS (SUSER_SNAME())
  

When a record is inserted into the main table, SQL Server does not insert anything into the history table. But the field CreatedBy records who created the record, because of the default constraint. But if/when the record gets updated, SQL Server inserts a record into the associated history table. The key idea here is that the name of the logged-in user who made the change is recorded into the history table, i.e. the contents of field LoggedInUser in the main table (which always contains the name of who is logged in to the connection) is saved into the field LoggedInUser in the history table.

That's almost what we want, but not quite - it's one change behind. E.g. if user Dave inserted the record, but user Andrew made the first update, "Andrew" is recorded as the user name in the history table, next to the original contents of the record that Dave inserted. However, all the information is there - it just needs to be unravelled. Joining the system generated fields for ROW START and ROW END, we get the user who made the change (from the previous record in the history table). However, there's no record in the history table for the originally inserted version of the record. In that case we retrieve the CreatedBy field.

This seems to provide a watertight auditing solution. Even if a user edits the field CreatedBy, the edit will be recorded in the history table. For that reason, we recover the oldest value for CreatedBy from the history table, instead of the current value from the main table.

Deleted records

The query above does not show who deleted records from the main table. This can be retrieved using the following (could be simplified?):

SELECT 

  d.EmployeeID, d.LoggedInUser AS DeletedBy, 

  d.CreatedBy, d.ValidFromUTC, d.ValidToUTC AS DeletedAtUTC

FROM

  (SELECT EmployeeID FROM dbo.EmployeeHistory GROUP BY EmployeeID) AS eh   -- list of IDs

OUTER APPLY

  (SELECT TOP 1 * FROM dbo.EmployeeHistory 

   WHERE EmployeeID = eh.EmployeeID 

   ORDER BY ValidFromUTC DESC) AS d -- last history record, which may be for DELETE

LEFT JOIN

  dbo.Employees AS e

    ON eh.EmployeeID = e.EmployeeID

WHERE e.EmployeeID IS NULL          -- record is no longer in main table

Sample table script

The above examples are based on the table script (history table is created by SQL Server):

CREATE TABLE dbo.Employees(

  EmployeeID INT /*IDENTITY(1,1)*/ NOT NULL,

  FirstName NVARCHAR(40) NOT NULL,

  Score INTEGER NULL,

  LoggedInUser AS (SUSER_SNAME()),

  CreatedBy NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()),

  ValidFromUTC DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(),

  ValidToUTC DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),

  CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (EmployeeID ASC),

  PERIOD FOR SYSTEM_TIME (ValidFromUTC, ValidToUTC)

) 

WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeeHistory ))

Edit: (2018/11/19) Added default constraints against the system_time fields, which is considered by some to be best practice, and helps if you're adding system-versioning to an existing table.

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.

In the current implementation of temporal tables, it records only time based information and nothing else about the session that made the change. And don't read that statement as me having some sort of insider knowledge that that situation may change in the future; I don't know anything about it. If you need that information, you will need to record it in row. A classic approach for doing that is to use a trigger that fires on DML operations and maintains that value on behalf of the user.

Another option that I was thinking about to solve this issue is to have a LastModifiedBy field in your base temporal table that is filled when the row is saved or updated.

This would show who modified the table and thus created the history record. As Aaron mentioned above, you could do it in a trigger, but my thought is to have it decided before it gets to the insert/update and put the value in the LastModifiedBy field at the time the record is updated.

This would then also be in the history table each time the record is modified.