Bintobin-logo
   
   

Audit Trail in SQL Server 2005, 2008 - Data Activity Monitoring Tool

Introduction: Audit Trail in SQL Server 2005, 2008 - Data Activity Monitoring is a tool through which we can record all user’s activity on the database, where users are interacting with database directly or through application. It is very useful for those who wish to keep eyes on user activity with data in the database. Also it is helpful for Q.A. (quality assurance team) who is testing application and wish to know data flow before to approve application. This tool is quite easy to manage and view report than other tools. The person who is not DBA can play with this tool easily. This tool is designed for management/administration department to audit data in database.  It is just like a CC camera on database.

Software Requirement: This tool is designed for MS SQL Server 2005 and 2008 all editions. This tool is compatible with .net framework 4.0 client profile, the link is given to download at the bottom of home page of www.bintobin.com. The system requirement for Audit Trail is same as requirement for  .net framework 4.0 Client Profile, to know more click link given at www.bintobin.com.

How to start: Create a new database in your server for Audit Trail only. Because audit trail keep all users activity logs in database, that’s why we are calling it Audit Log Database. The user’s activity information is kept in Audit Log Database. You can use one Audit Log Database for multiple databases which does not have same table structure or name.

Step By Step: Download Dot Net Framework 4.0 for Audit Trail from www.bintobin.com.                           Install Dot Net Framework.                                  Download Audit Trail version 2.0 from www.bintobin.com and run it by double clicking.

History Of Row in a Table: Data Activity History will show you all history captured since audit trail was executed on the table. There could be several actions like Insert/Initial, Update, Delete and Current. If user made changes in a row, which is exist before running audit trail on table, the row will be marked as Initial with additional user action. It also show you Current status of row after any action taken by user.

Application User: Application user is the user-ID through which user login the application, if application is designed with login page to let user identify for application. Most of the developers are saving login user-ID in the each table during any action taken by the user. But when a user has deleted the row then you cannot identify who has deleted. That’s why I recommend, Developers must set ContextInfo with UserID each time when application is connecting with database.

ContextInfo(): Context Info is used as a session in sql server for each connection. The software company who is providing login page to authenticate user, must Set Context Info() value with Login User-ID during each time after establishing connection with SQL Server. Through context info(), Audit Trail can show you each row history with proper application user.

Reporting SDK: It is an example with source code written in C#.net. The Software company can attach source code for Audit Trail Reporting with their software, or they can develop same reporting system in web application.

Audit Database: The database on which you wish to keep eye.

Audit Log Database: The database where you wish to keep audit log information.

Audit Table: The table on which you run audittrail.

keywords:Audit Trail, AuditTrail, Audit Data, Audit Database, audit log database, Audit Log, Audit Table, Data Activity, user doing in application, application user, application log, changed data, data log, data history, trigger to audit, trigger for data log, context info for user name, contextinfo for user, contextinfo user, row log, history of row, row history, QA tool, Q.A. Tool, database quality assurance, capture data changes, approve database, track database changes, verify database, data flow , activity monitoring, camera on database, keep eye on user activity, activity on the database, user activity with data, security tool