Who Made That Change? Low Rent User Auditing Using Temporal Tables

I Don’t Find This Stuff Fun

---

ED: I moved up this post’s publication date after Mr. O posted this question. So, Dear Brent, if you’re reading this, you can consider it my humble submission as an answer.

It’s really not up my alley. I love performance tuning SQL Server, but occasionally things like this come up.

Sort of recently, a client really wanted a way to figure out if support staff was manipulating data in a way that they shouldn’t have. Straight away: this method will not track if someone is inserting data, but inserting data wasn’t the problem. Data changing or disappearing was.

The upside of this solution is that not only will it detect who made the change, but also what data was updated and deleted.

It’s sort of like auditing and change data capture or change tracking rolled into one, but without all the pesky stuff that comes along with auditing, change tracking, or change data capture (though change data capture is probably the least guilty of all the parties).

Okay, so here are the steps to follow. I’m creating a table from scratch, but you can add all of these columns to an existing table to get things working too.

Robby Tables

---

First, we create a history table. We need to do this first because there will be computed columns in the user-facing tables.

/*
Create a history table first
*/
CREATE TABLE
    dbo.things_history
(
    thing_id int NOT NULL,
    first_thing nvarchar(100) NOT NULL,
    original_modifier sysname NOT NULL, 
        /*original_modifier is a computed column below, but not computed here*/
    current_modifier sysname NOT NULL, 
        /*current_modifier is a computed column below, but not computed here*/
    valid_from datetime2 NOT NULL,
    valid_to datetime2 NOT NULL,
    INDEX c_things_history CLUSTERED COLUMNSTORE
);

I’m choosing to store the temporal data in a clustered columnstore index to keep it well-compressed and quick to query.

Next, we’ll create the user-facing table. Again, you’ll probably be altering an existing table to add the computed columns and system versioning columns needed to make this work.

/*Create the base table for the history table*/
CREATE TABLE
    dbo.things
(
  thing_id int
      CONSTRAINT pk_thing_id PRIMARY KEY,
  first_thing nvarchar(100) NOT NULL,
  original_modifier AS /*a computed column, computed*/
      ISNULL
      (
          CONVERT
          (
              sysname,
              ORIGINAL_LOGIN()
          ),
          N'?'
      ),
  current_modifier AS /*a computed column, computed*/
      ISNULL
      (
          CONVERT
          (
              sysname,
              SUSER_SNAME()
          ),
          N'?'
      ),
  valid_from datetime2
      GENERATED ALWAYS AS
      ROW START HIDDEN NOT NULL,
  valid_to datetime2
      GENERATED ALWAYS AS
      ROW END HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME
  (
      valid_from,
      valid_to
  )
)
WITH
(
    SYSTEM_VERSIONING = ON  
    (
        HISTORY_TABLE = dbo.things_history,
        HISTORY_RETENTION_PERIOD = 7 DAYS
    )
);

A couple things to note: I’m adding the two computed columns as non-persisted, and I’m adding the system versioning columns as HIDDEN, so they don’t show up in user queries.

The WITH options at the end specify which table we want to use as the history table, and how long we want to keep data around for. You may adjust as necessary.

I’m tracking both the ORIGINAL_LOGIN() and the SUSER_SNAME() details in case anyone tries to change logins after connecting to cover their tracks.

Inserts Are Useless

---

Let’s stick a few rows in there to see how things look!

INSERT
    dbo.things
(
    thing_id,
    first_thing
)
VALUES
    (100, N'one'),
    (200, N'two'),
    (300, N'three'),
    (400, N'four');

Okay, like I said, inserts aren’t tracked in the history table, but they are tracked in the main table.

If I do this:

EXECUTE AS LOGIN = N'ostress';
INSERT
    dbo.things
(
    thing_id,
    first_thing
)
VALUES
    (500, N'five'),
    (600, N'six'),
    (700, N'seven'),
    (800, N'eight');

And then run this query:

SELECT
    table_name =
        'dbo.things',
    t.thing_id,
    t.first_thing,
    t.original_modifier,
    t.current_modifier,
    t.valid_from,
    t.valid_to
FROM dbo.things AS t;

The results won’t make a lot of sense. Switching back and forth between the sa and ostress users, the original_modifier column will always say sa, and the current_modifier column will always show whichever login I’m currently using.

You can’t persist either of these columns, because the functions are non-deterministic. In this way, SQL Server is protecting you from yourself. Imagine maintaining those every time you run a different query. What a nightmare.

The bottom line here is that you get no useful information about inserts, nor do you get any useful information just by querying the user-facing table.

Updates And Deletes Are Useful

---

Keeping my current login as ostress, let’s run these queries:

UPDATE 
    t
SET 
    t.first_thing =
        t.first_thing +
        SPACE(1) +
        t.first_thing
FROM things AS t
WHERE t.thing_id = 100;

UPDATE 
    t
SET 
    t.first_thing =
        t.first_thing +
        SPACE(3) +
        t.first_thing
FROM things AS t
WHERE t.thing_id = 200;

DELETE
    t
FROM dbo.things AS t
WHERE t.thing_id = 300;

DELETE
    t
FROM dbo.things AS t
WHERE t.thing_id = 400;

Now, along with looking at the user-facing table, let’s look at the history table as well.

To show that the history table maintains the correct original and current modifier logins, I’m going to switch back to executing this as sa.

Alright, so here’s what we have now!

In the user-facing table, we see the six remaining rows (we deleted 300 and 400 up above), with the values in first_thing updated a bit.

Remember that the _modifier columns are totally useless here because they’re calculated on the fly every time

We also have the history table with some data in it finally, which shows the four rows that were modified as they existed before, along with the user as they logged in, and the user as the queries were executed.

This is what I would brand “fairly nifty”.

FAQ

---

Q. Will this work with my very specific login scenario?

A. I don’t know.

 

Q. Will this work with my very specific set of permissions?

A. I don’t know.

 

Q. But what about…

A. I don’t know.

I rolled this out for a fairly simple SQL Server on-prem setup with very little insanity as far as login schemes, permissions, etc.

You may find edge cases where this doesn’t work, or it may not even work for you from the outset because it doesn’t track inserts.

With sufficient testing and moxie (the intrinsic spiritual spark, not the sodie pop) you may be able to get it work under you spate of local factors that break the peace of my idyllic demo.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.