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:
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:
Then create a trigger that updates the edited column on UPDATE events:
Some notes about this:
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.
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:
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:
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:
Now, the changes to the stored procedure are simple:
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:
So, try out these examples, and you can see how the trigger captured all of your auditing:
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):
Now, only a couple of minor changes to the trigger will capture this data as well:
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:
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:
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.
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?