Problem solve Get help with specific problems with your technologies, process and projects.

Using Audit Trail, part 1

How can I do Audit Trail on any SQL Server table? I would like to know the particular table's modified field, previous and new value, who, date and time.

Data auditing has become a business challenge for many DBAs and security administrators with the recent compliance dates required by numerous industries from the United States Government. Some of these regulations include:

  • Healthcare – HIPAA
  • Pharmaceuticals and FDA – 21 CFR Part 11
  • Financial – GLBA
  • Services – SAS 70
  • Publicly Traded Companies – SEC
  • Telephone Carriers – CPNI
  • Financial Accountability – SOX
Organizations also impose internal auditing regulations based on internal needs and from clients via service level agreements (SLAs). Although the source of the compliance may be different, the reality is that organizations are faced with numerous regulations and must prove compliance on a regular basis or face stiff fines, imprisonment or law suits.

As a DBA, this legislation provides you with an opportunity to be the hero for your organization to help prove compliance. As such, you have a number of options to achieve compliance via comprehensive processes, talented people and beneficial technology. These options include the following:

  • Triggers can be created for each table and executed any time an INSERT, UPDATE or DELETE transaction is committed in conjunction with audit tables to capture the needed data with functions like GETDATE(), SUSER_SNAME() as well as accessing the values in the INSERTED and DELETED tables internal to the trigger code.
  • In your existing stored procedures it is possible to mimic the trigger functionality as outlined above and for SELECT statements execute INSERT statements to capture the necessary values into an audit table.
  • SQL Profiler is able to capture all of the SQL Server activity both from a transactional and data perspective in a raw format.
  • Fn_get_sql is a function introduced in SQL Server 2000 that can be called for each SPID and record the activity performed for each process.
  • DBCC INPUTBUFFER is another statement that can be issued on a per SPID basis to capture the user statements.
  • C2 Auditing is another option with SQL Server to capture the processing information and store externally for review.
  • ApexSQL's Audit is a tool to manage triggers for database tables in a particular database.
  • Lumigent Log Explorer, Log PI and ApexSQL Log Navigator leverage the contents of the database transaction log in a similar manner to determine the data changes at a transactional level.
  • Lumigent Entegra is an enterprise auditing solution that records all database activity (SELECT, INSERT, UPDATE, DELETE, DDL, DML, Security, etc.) without the overhead of triggers and can not be subverted with the ability to provide real time alerting as well as centralized storage for compliance reporting via a simple Web-based interface.

This response is continued...

This was last published in January 2004

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.