Auditing in SQL Server 2000

In the past, the question of how to perform auditing under SQL Server has had a simple answer: Do it yourself. But now, SQL Server 2000 has eased the burden of auditing by introducing an extensive and configurable auditing system built on the SQL Trace tool.

The most important auditing feature for average users is the ability to track modifications made to data. Unfortunately, while SQL Server 2000 can indicate which user might have updated a table, it cannot capture the actual content of the update. Still, SQL Server 2000 does conform with the C2-level auditing required by certain government users.

Let's take a quick look at the evolution of SQL Server auditing, then dive into its capabilities.

SQL Server auditing prior to SQL Server 2000

Earlier releases of SQL Server have only one built-in auditing feature: the ability to capture server logins. Under SQL Server 7.0, for example, you can specify that logins -- both successful and failed -- be written to the Windows 2000 (or Windows NT) application log, which you view using the Event Viewer. To start this process, you specify an audit level via the Audit Level option in the Enterprise Manager (on the Server Properties Security tab). The SQL Server 7.0 documentation erroneously states that this audit facility will also write login attempts to SQL Server's own log. It does not.

To audit SQL Server 7.0 activity on a deeper level, you could use the Profiler to capture both successful logins and SQL statements issued by users. But beware: overhead is high, since SQL Server 7.0's Profiler was built to analyze performance and tuning, not to conduct auditing. Also, the Profiler captures only successful commands and logins; if a user issues a SQL command that is denied, for example, the SQL Server 7.0 Profiler will not report it.

SQL Trace's new role and new capabilities

SQL Trace is the heart of SQL Server 2000's auditing capabilities. In fact, Microsoft resurrected SQL Trace, which had been downplayed in SQL Server 7.0, specifically to facilitate auditing.

SQL Trace, the server-side component of SQL Server 2000's tracing system, works with the Profiler client-side viewing utility. (The functionality and naming of SQL Trace and Profiler have changed with each SQL Server release since version 6.5. See the sidebar for details.) Traces, which are activated by issuing a SQL Trace stored procedure, incur much lower overhead than did SQL Server 7.0's Profiler.

Sign up for ITworld's Daily newsletter
Follow ITworld on Twitter @IT_world

• Email
• Print
• Share
  • Slashdot
  • Digg
  • Stumble
  • Reddit
  • Newsvine