Transact-SQL Cookbook

Chapter 6. Audit Logging

A log is a record of activities or a record of changes to the data in an object, such as a table. Logs can be implemented and used for many purposes. SQL Server implements database logs that are used to maintain database integrity and consistency, improve performance, and help you identify problems. Examples of such logs are transactional logs, cache logs, and disk logs. Some of these logs are accessible, but none of them is really designed to be useful in an auditing situation.

If you have a critical database and you want to keep an accurate record of user activity against that database, you can implement a system of audit logs. That’s what the recipes in this chapter address. An audit log allows you to track the changes that users make to a table. You can find out who did what and when. Audit logs also allow you to track other activity in a system. If implemented correctly, you can even use an audit log to view a snapshot of a table’s data as of a specific point in time.

Audit Logs

In this chapter, we’ll show you how to implement an audit log that can be used to track changes to the data in a table or that can be used to track a user’s activities at a higher level. Before you begin, there are several things that you need to think about. These include:

• Your objectives for the log

• Whether you want to log row-level changes or higher-level activities

• Whether you want to implement the log as a database table or as an operating-system file

• The storage unit that ...