Published on

June 29, 2009

Tracking SQL Server Modifications and Auditing

Have you ever wondered when a table was modified, dropped, or created in your SQL Server database? Or perhaps an auditor asked when the last time the sa id logged in, even though login auditing is turned off? These are common questions that can be difficult to answer without the right tools. While SQL Server 2005 introduced some new features to help with these scenarios, what about those of us still using SQL Server 2000?

In this article, we will walk through a solution that has been implemented on servers that are SOX Compliant and servers where developers have too much access for a DBA’s peace of mind. This solution involves tracking DDL statement execution using a combination of SQL Server tools and views.

The solution consists of several components:

  • DTS – SQL_TRACE_WORKFLOW – stops the current trace, loads data, and restarts the trace
  • DTS – SQL_START_TRACE – called by SQL_TRACE_WORKFLOW to start the trace
  • DTS – SQL_CHECK_TRACE – verifies the current trace is running and restarts if necessary
  • audit_traceserver.sql – executed on the audit server and creates the required database and objects
  • audit_repository.sql – executed on the storage server and creates the required database and objects
  • Audit_check_job.sql – a job that verifies the trace is running and restarts if necessary
  • Audit_job.sql – starts the trace when the SQL Agent starts and cycles the log file at a predefined interval
  • Sqltrace.trc – an empty file required for the initial start of auditing
  • SQLProfiler_Audit.tdf – a trace template that was used to build the audit script

The server being audited and the repository server can be the same server or separate. This method allows for the storage of data on a server that can be shared by many collection processes.

To install the solution, follow these steps:

  1. Connect to the traced server and open the script file audit_traceserver.sql. This script will create a database named trace_repository along with the necessary objects.
  2. Execute the script once values for tbl_traceconfig have been verified.
  3. Save the file sqltrace.trc in the directory specified in the Path location specified in tbl_traceconfig.
  4. Connect to the repository server and open the script file audit_repository.sql. This script will create the necessary objects if needed.
  5. Execute the script once verified.

The solution also includes DTS packages that handle the installation, configuration, and explanation of the process. These packages are stored on the repository server and include tasks such as starting and stopping the trace, loading data, and archiving trace files.

Additionally, there are two jobs: Audit_job and Audit_Check_Job. Audit_job is executed from the repository server to call SQL_TRACE_WORKFLOW, while Audit_Check_Job is executed to call SQL_CHECK_TRACE. These jobs should be scheduled to run at regular intervals to ensure data collection and trace status verification.

It is important to note that if the TraceServer and Repository server are different, some modifications to the configuration may be required.

By implementing this solution, you can track and audit modifications made to your SQL Server database. While it may not be as powerful as third-party tools, it provides a cost-effective option for auditing and history keeping.

For more detailed information on how the system functions and procedures work, refer to the SQL Server Books Online documentation.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.