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')

3 comments:

  1. Is it Working on MSSQL 2008R2 Express Edition.

    I couldn't use it following the instruction.

    ReplyDelete
  2. this is not available in express edition

    ReplyDelete
  3. All of these self built auditing solutions rely on one simple fact:
    "the DBA(s) can be trusted"
    but often those closest to the data are the highest risk.
    Then there is the performance impact of triggers!!!

    The question that will be asked by any auditor or legal inquiry is: "how can we trust this audit information"

    The only way to a have a trusted audit trail is to use a 3rd party solution with tamper protection etc built-in. Suggest you look at Idera's SQL compliance manager - DDL, DML and system event auditing all included with alerts.

    ReplyDelete