How do I audit changes to SQL Server data?

Many people add a TIMESTAMP column to a table, thinking that it will tell them when a row has changed. It will, sort of. If you know what the TIMESTAMP value was before, and you have it available to compare to the current value, then you know it has been updated, but not when. See, the TIMESTAMP datatype does not have a very appropriate name -- it does not contain date or time information at all, it is simply a unique identifier that changes when the row is updated. Take the following example: 
 

SET NOCOUNT ON    USE PUBS  GO    CREATE TABLE dbo.foo  (      bar INT PRIMARY KEY,      mod TIMESTAMP  )  GO    INSERT dbo.foo(bar) VALUES(1)    SELECT bar, mod FROM foo    UPDATE dbo.foo SET bar = 2    SELECT bar, mod FROM dbo.foo    DROP TABLE dbo.foo

 
Results: 
 

bar mod   --- ------------------   1 0x00000000000010ED    bar mod   --- ------------------   2 0x00000000000010EE

 
It may look, in this example, that the TIMESTAMP values are sequential. They are, but only because this is a very isolated test. In a busier system, and with a greater lag time between modifications, it won't be so predictably linear. And it still won't give you any idea about the time that has lapsed between updates, or when the most recent update took place. 
 
So, if you want a human being (or even code) to be able to decipher when a row was last modified, TIMESTAMP is not likely to be a good choice. 
 
Instead, I would start by doing it this way. Let's add the requirement that you also want to know when the row was added (this value will be populated on INSERT, and should never change for the life of the row). Create two DATETIME columns: 
 

CREATE TABLE dbo.foo  (      bar INT PRIMARY KEY,       added DATETIME DEFAULT GETDATE(),      edited DATETIME  )  GO

 
Then create a trigger that updates the edited column on UPDATE events: 
 

CREATE TRIGGER dbo.updateFooBar  ON dbo.foo  FOR UPDATE  AS  BEGIN      IF NOT UPDATE(edited)          UPDATE dbo.foo SET edited=GETDATE()          WHERE bar IN (SELECT bar FROM inserted)  END  GO

 
Some notes about this:

• I used DATETIME for demonstration purposes, but you can probably halve your storage requirements by using SMALLDATETIME instead, since it's unlikely that you need precision lower than to-the-minute; 
   
  
• I use IF NOT UPDATE(edited) to prevent recursion. Otherwise, if the trigger fires an UPDATE statement, then that UPDATE statement will fire the trigger, and cetera ad nauseum; and, 
   
  
• I use IN rather than equality so that it supports multiple-row updates. Otherwise, if you ue a JOIN on the INSERTED table with equality, there is a chance you would only capture the first row in a multi-row update. If you use WHERE bar = (SELECT ...), you will likely get the following error: 
   
  

  Server: Msg 512, Level 16, State 1, Line 13  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So, let's test it: 
 

INSERT foo(bar) VALUES(1)  INSERT foo(bar) VALUES(2)    SELECT bar, added, edited FROM foo    WAITFOR DELAY '00:00:10'    UPDATE foo SET bar = bar + 5    WAITFOR DELAY '00:00:05'    UPDATE foo SET bar = bar + 5 WHERE bar = 7    SELECT bar, added, edited FROM foo    DROP TABLE foo

 
Want to take it one step further? Let's also add the name of the user who added updated the row... this is most effective in environments where users are authenticated and are using their domain username, rather than sharing a generic SQL Server user in their connection strings. This time we have an INSERT TRIGGER as well, to capture the name of the user who initially populated the row. 
 

CREATE TABLE dbo.foo  (      bar INT PRIMARY KEY,       added DATETIME DEFAULT GETDATE(),      edited DATETIME,      editor VARCHAR(64)  )  GO    CREATE TRIGGER dbo.insertFooBar  ON dbo.foo  FOR INSERT  AS  BEGIN      UPDATE dbo.foo SET editor=USER_NAME(USER_ID())          WHERE bar IN (SELECT bar FROM inserted)  END  GO    CREATE TRIGGER dbo.updateFooBar  ON dbo.foo  FOR UPDATE  AS  BEGIN      IF NOT UPDATE(edited)          UPDATE dbo.foo              SET edited=GETDATE(),              editor=USER_NAME(USER_ID())              WHERE bar IN (SELECT bar FROM inserted)  END  GO    INSERT foo(bar) VALUES(1)  INSERT foo(bar) VALUES(2)    SELECT bar, added, edited, editor FROM foo    WAITFOR DELAY '00:00:10'    UPDATE foo SET bar = bar + 5    WAITFOR DELAY '00:00:05'    UPDATE foo SET bar = bar + 5 WHERE bar = 7    SELECT bar, added, edited, editor FROM foo    DROP TABLE foo

 

---

A more complex example 
 
You can also write your own logging system, and have the utmost control not only over exactly what is logged, but also how long those logs are kept. Let's say you have an employees table: 
 

CREATE TABLE dbo.Employee  (      EmployeeID INT IDENTITY(1,1)          PRIMARY KEY CLUSTERED,      FullName VARCHAR(32) NOT NULL,      Salary INT NOT NULL  )  GO

 
Obviously, this data is very sensitive in nature, and you probably have column-level permissions to prevent unauthorized users from seeing (never mind editing) a person's salary. However, it still might be useful to log changes to this table. So, imagine you already have a stored procedure used to modify the table: 
 

CREATE PROCEDURE dbo.editEmployee      @employeeID INT = 0,      @fullName VARCHAR(32),      @salary INT,      @delete BIT = 0  AS  BEGIN      SET NOCOUNT ON      IF @delete = 0      BEGIN          IF EXISTS          (              SELECT 1              FROM Employee              WHERE EmployeeID = @employeeID          )          BEGIN              UPDATE Employee              SET FullName = @fullName,              Salary = @salary              WHERE EmployeeID = @employeeID          END          ELSE          BEGIN              INSERT Employee(FullName, Salary)              VALUES(@fullName, @salary)          END      END      ELSE      BEGIN          DELETE Employee          WHERE EmployeeID = @employeeID      END  END  GO

 
We can easily modify this to add tracking, to determine what changes were made to what row, and by whom. First, we need a table: 
 

CREATE TABLE dbo.AuditEmployee  (      EmployeeID INT,      UserName VARCHAR(255),      Operation CHAR(1)      CHECK Operation IN ('U', 'I', 'D', 'S'),      dt SMALLDATETIME NOT NULL          DEFAULT GETDATE()  )

 
Now, the changes to the stored procedure are simple: 
 

CREATE PROCEDURE dbo.editEmployee      @employeeID INT = 0,      @fullName VARCHAR(32),      @salary INT,      @delete BIT = 0  AS  BEGIN      SET NOCOUNT ON      IF @delete = 0      BEGIN          IF EXISTS          (              SELECT 1              FROM Employee              WHERE EmployeeID = @employeeID          )          BEGIN              UPDATE Employee              SET FullName = @fullName,              Salary = @salary              WHERE EmployeeID = @employeeID                INSERT AuditEmployee              (EmployeeID, UserName, Operation)              VALUES(@employeeID, SUSER_SNAME(), 'U')          END          ELSE          BEGIN              INSERT Employee(FullName, Salary)              VALUES(@fullName, @salary)                INSERT AuditEmployee              (EmployeeID, UserName, Operation)              VALUES(SCOPE_IDENTITY(), SUSER_SNAME(), 'I')          END      END      ELSE      BEGIN          DELETE Employee          WHERE EmployeeID = @employeeID            INSERT AuditEmployee          (EmployeeID, UserName, Operation)          VALUES(@employeeID, SUSER_SNAME(), 'D')      END  END  GO

 
If this is a web application, make sure your web users are authenticating against the web site using Windows Authentication, and that their credentials are passed along in the connection string (see Article #2126). Otherwise, SUSER_SNAME() will be the anonymous user, and your tracking will be for naught. If you need to use anonymous authentication (e.g. for off-site users) then you could pass a session variable in, instead of relying on SUSER_SNAME(). 
 
Now, what if you have several stored procedures, and you don't wish to change all of them? Or if you are not controlling data access exclusively through stored procedures (you really should be)? Well, you can use triggers instead of the above modifications. Keeping the original tables in place, you can have the following: 
 

CREATE TRIGGER Audit      ON dbo.Employee      FOR INSERT, UPDATE, DELETE  AS      IF (SELECT COUNT(*) FROM inserted) > 0      BEGIN          IF (SELECT COUNT(*) FROM deleted) > 0          BEGIN              -- update!              INSERT AuditEmployee              (EmployeeID, UserName, Operation)              SELECT EmployeeID, SUSER_SNAME(), 'U'              FROM inserted          END          ELSE          BEGIN              -- insert!              INSERT AuditEmployee              (EmployeeID, UserName, Operation)              SELECT EmployeeID, SUSER_SNAME(), 'I'              FROM inserted          END      END      ELSE      BEGIN          -- delete!          INSERT AuditEmployee          (EmployeeID, UserName, Operation)          SELECT EmployeeID, SUSER_SNAME(), 'D'          FROM deleted      END  GO

 
So, try out these examples, and you can see how the trigger captured all of your auditing: 
 

INSERT Employee(FullName, Salary)      VALUES('Aaron Bertrand', 10000)    INSERT Employee(FullName, Salary)      VALUES('Joe Schmoe', 15000)    UPDATE Employee SET salary = 30000      WHERE EmployeeID = 1    -- handles multiple rows as well!  -- let's give everyone a raise to demonstrate    UPDATE Employee SET Salary = Salary + 2000    DELETE Employee WHERE EmployeeID = 2      SELECT * FROM Employee  SELECT * FROM AuditEmployee

 
Now, you might want to store the name and salary of a deleted employee, since once they are deleted, having a record of only the employeeID might not be very useful. You might also want to track changes in salary, since the old value is deleted when an update occurs. So, let's create another table to track these things (admittedly, this is not optimal design): 
 

CREATE TABLE dbo.AuditEmployeeDetails  (      EmployeeID INT,      UserName VARCHAR(255),      Operation CHAR(1)      CHECK (Operation IN ('U', 'D')),      FullName VARCHAR(32) NOT NULL,      OldSalary INT NULL,      NewSalary INT NULL,  )  GO

 
Now, only a couple of minor changes to the trigger will capture this data as well: 
 

CREATE TRIGGER Audit      ON dbo.Employee      FOR INSERT, UPDATE, DELETE  AS      IF (SELECT COUNT(*) FROM inserted) > 0      BEGIN          IF (SELECT COUNT(*) FROM deleted) > 0          BEGIN              INSERT AuditEmployee(EmployeeID, UserName, Operation)                  SELECT EmployeeID, SUSER_SNAME(), 'U'                  FROM inserted                INSERT AuditEmployeeDetails              (                  EmployeeID,                  Operation,                  FullName,                  OldSalary,                  NewSalary,                  UserName              )              SELECT                   i.employeeID,                  'U',                  i.FullName,                  d.salary,                  i.salary,                  SUSER_SNAME()              FROM                  inserted i              INNER JOIN                  deleted d              ON                  i.employeeID = d.employeeID                  -- only if salary changed                  AND i.salary != d.salary          END          ELSE          BEGIN              INSERT AuditEmployee(EmployeeID, UserName, Operation)                  SELECT EmployeeID, SUSER_SNAME(), 'I'                  FROM inserted          END      END      ELSE      BEGIN          INSERT AuditEmployee(EmployeeID, UserName, Operation)              SELECT EmployeeID, SUSER_SNAME(), 'D'              FROM deleted            INSERT AuditEmployeeDetails          (              EmployeeID,              Operation,              FullName,               OldSalary,              UserName          )          SELECT              employeeID,              'D',              FullName,              Salary,              SUSER_SNAME()          FROM              deleted      END  GO

 
Now, go ahead and run the same script as above, and you'll see that the AuditEmployeeDetails table contains information for both updated and deleted rows. Which, of course, can be very useful when trying to determine why the mailroom guy is suddenly driving a better car than the CEO! 
 
Another thing you might want to track is who is *viewing* employee records. There is no way to implement a trigger on SELECT of a table, so in order to do this, you're going to have to control access to the table through stored procedures (this means denying SELECT access to the table itself, which probably makes sense when dealing with salaries). So, let's say you have the following stored procedure to return details for an employee: 
 

CREATE PROCEDURE dbo.getEmployeeDetails      @employeeID INT  AS  BEGIN      SET NOCOUNT ON        SELECT          FullName, Salary          FROM Employee          WHERE EmployeeID = @employeeID  END  GO

 
To track the users that are executing this stored procedure, and which employee(s) they are looking at, we only need to make a small adjustment: 
 

CREATE PROCEDURE dbo.getEmployeeDetails      @employeeID INT  AS  BEGIN      SET NOCOUNT ON        SELECT          FullName, Salary          FROM Employee          WHERE EmployeeID = @employeeID        INSERT AuditEmployee          (EmployeeID, UserName, Operation)          VALUES(@employeeID, SUSER_SNAME(), 'S')  END  GO

 
There are many things I didn't handle here. For example, you would probably want to make the AuditEmployee insert a stored procedure, to make the trigger code much lighter. And you might want constraints, cascading foreign keys, and indexes. And certainly, my table design is not optimal; I merely adapted as I went along to show the breadth of data points you can track. 
 

---

 
If you want to globally monitor changes to your data, and can't be bothered with setting up scripts, you can either have Profiler running, or look into a 3rd party product like Lumigent's Audit DB. For a more exhaustive list of 3rd party products, see Article #2496.