Audit Trail in SQL Server 2000, 2005 & 2008

Audit Trail in SQL Server 2000, 2005&2008 how to audit data changes in sql server,auditing in sql server,audit changes in sql server

Download
Audit.SQL

Audit the changes in the Data in SQL Server tables is one of the most common requirements. In this article, Ill demonstrate the way to audit a SQL Server database data that is both simple and flexible in 2000, 2005 and 2008.

Audit in SQL Server 2000 and 2005 using triggers.

In SQL Server 2000 or 2005 the only option available at the database back-end to audit the data is through triggers. If the application owner or any user want to audit the data for a particular table for some time then he may need to contact the database developer to write the trigger to audit.
This script allows anyone to configure the audit without having any knowledge on writing T-SQL.

Features of this Audit Setup:

· Configure the audit at the table level, column level

· Allows the user to modify the actions (insert/update/delete)

· Allows the user to disable and can enable the audit as and when needed.

· Allows the user to track the changes through the date and login name

 

Step 1: Download and Execute the "AUDIT.SQL.TXT" script

Step 2: insert a record in the AUDIT_CONFIGURE table as the below sample data.

Sample data in the AUDIT_CONFIGURE table to audit the customers table whenever age and phone columns are updated

tbl_name	action	updated_ collist	is_ enable	aud_operation_ col_name	aud_date_ colname	aud_created_ by_col _name
customers	update	age, phone	1	NULL	NULL	NULL

Step 3: Test Audit Trail

Once you enter a record in the AUDIT_CONFIGURE table then it will create a trigger “TblName_TRG_AUD>” on the user tables (in this case the trigger name will be customers_ TRG_AUD) and creates an audit table “AUD_” to log the audit (in this case the table name will be AUD_customers).

How it works:

Once you execute the attached script, it will create 3 objects table 1- “AUDIT_CONFIGURE” with a trigger on it 2-"TRG_AUDIT_CONFIGURE" this trigger calls a procedure 3- "PR_GEN_TRIGGER" which will create the triggers at runtime with a name “_ TRG_AUD” on the user tables and creates an audit table “AUD_” for each user table.

AUDIT_CONFIGURE TBL_NAME ACTION UPDATED_COLLIST IS_ENABLE AUD_OPERATION_COL_NAME AUD_DATE_COL_NAME AUD_CREATED_BY_COL_NAME

+TRG_AUDIT_CONFIGURE()

à PR_GEN_TRIGGER() ↓ TblName_TRG_AUD + AUD_TblName

Notes:

1. As the trigger on the AUDIT_CONFIGURE table will create the audit log tables with a name AUD_ and trigger with a name _TRG_AUD> ,make sure that you don’t have any objects with these names exists before doing the audit.

2. If the main table which you are going to audit contains any of these column names below then it will raise an error while creating the audit log table as the audit log table AUD_ will be created using the “select into AUD_ from tbl_name” and adds the below 3 columns using the “alter table AUD_” statement.To avoid this error mention different column names for

Aud_operation_col_name

aud_date_colname

aud_created_by_col_name

Audit in SQL Server 2008

SQL Server 2008 has the inbuilt feature to capture the changed data. By using change data capture, you eliminate expensive techniques such as user triggers to capture the data.

Attached file AUDITING_2008 contains the below script.

use ADVENTUREWORKS -- Make sure that the SQL Server agent is running , as the data capture will be triggered by the job in SQL Server agent. EXEC sp_cdc_enable_db GO -- Check the is_cdc_enabled status in sys.databases table select name from sys.databases where is_cdc_enabled = 1 GO -- Create a test table CREATE TABLE dbo.customers( CustomerID int Primary Key NOT NULL, CustomerName nvarchar(100) NOT NULL, CustomerAddress nvarchar(100) NOT NULL) GO -- Make sure that the SQL Server Agent is running else you will recieve the below warning -- Warning : SQLServerAgent is not currently running so it cannot be notified of this action. EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'customers', @role_name = NULL, @supports_net_changes = 1 GO -- If the SQLServerAgent is stopped then it will print : SQLServerAgent is not currently running so it cannot be notified of this action. INSERT INTO dbo.customers values (001, N'Mike', N'SanRamon,CA') GO UPDATE dbo.customers SET CustomerName = N'Chandra' WHERE CustomerID = 001; GO -- now you can see few records in the audit table -- which will contains all the columns from the table and other information -- including pointers to the log and type of operation(1-Delete,2-Insert- -- 3 update (value before the update operation). This value applies only when the row filter option 'all update old' is specified. -- 4 update (value after the update operation) -- but its showing 3 records for 2 transactions,to get the detailed report of what being modified use the below script. select * from cdc.DBO_customers_CT GO -- to get the complete report what being modified. -- Here you can see only the records which are being modified DECLARE @from_lsn binary(10), @to_lsn binary(10); SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', GETDATE()-1); SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE()); SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customers(@from_lsn, @to_lsn, 'all') GO -- to display the last recent transaction DECLARE @from_lsn binary(10), @to_lsn binary(10); SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', GETDATE()-1); SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE()); SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_customers(@from_lsn, @to_lsn, 'all')