Auditing SQL Server Activity

Microsoft® SQL Server™ 2000 provides auditing as a way to trace and record activity that has happened on each instance of SQL Server (for example, successful and failed logins). SQL Server 2000 also provides an interface, SQL Profiler, for managing audit records. Auditing can only be enabled or modified by members of the sysadmin fixed security role, and every modification of an audit is an auditable event.

There are two type of auditing:

• Auditing, which provides some level of auditing but does not require the same number of policies as C2 auditing.
  
  
• C2 auditing, which requires that you follow very specific security policies. For more information about C2 auditing, see C2 Auditing.

Both types of auditing can be done by using SQL Profiler.

Using SQL Profiler

SQL Profiler provides the user interface for auditing events. There are several categories of events that can be audited using SQL Profiler, such as:

• End user activity (all SQL commands, logout/login, enabling of application roles).
  
  
• DBA activity (DDL, other than grant/revoke/deny and security events, Configuration (DB or server).
  
  
• Security events (grant/revoke/deny, login user/role add/remove/configure).
  
  
• Utility events (backup/restore/bulk insert/BCP/DBCC commands.
  
  
• Server events (shutdown, pause, start).
  
  
• Audit events (add audit, modify audit, stop audit).

For more information about what categories of events can be monitored, see Security Audit Event Category.

It is possible to audit the following aspects of SQL Server through SQL Profiler:

• Date and time of event.
  
  
• User who caused the event to occur.
  
  
• Type of event.
  
  
• Success or failure of the event.
  
  
• The origin of the request (for example, the Microsoft Windows NT® 4.0 computer name).
  
  
• The name of the object accessed.
  
  
• Text of the SQL statement (passwords replaced with ****).
  
  
• If you are a member of the sysadmin or securityadmin fixed server role and you reset your own password by using sp_password with all three arguments specified ('old_password', 'new_password', 'login'), the audit record will reflect that you are changing someone else's password.

Auditing can have a significant performance impact. If all audit counters are turned on for all objects, the performance impact could be high. It is necessary to evaluate how many events need to be audited compared to the resulting performance impact. Audit trail analysis can be costly, so it is recommended that audit activity be run on a server separate from the production server.

Note  If SQL Server is started with the -f flag, auditing will not run.

See Also

Monitoring with SQL Profiler