Enable Auditing in SQL Server 2005

Question

Hi All,

I want to enable auditing in SQL Server 2005. How can I do it with minimum efforts. Previously the auditing was enabled but somehow it is disabled now.

Thanks in advance,
Best Regards.

Answer 1

Article: "How to enable SQL 2005 Database Auditing

This article provides you with the steps to enable database auditing on Microsoft SQL 2005.
 
Configuration process for database auditing on the database-side (MS SQL) consists of the following steps:
 1.Create directory for the trace files (ex: C:\DbTrace). Trace files contain following audit data:
 ◦The data that was modified
 ◦Start and End times of the transaction
 ◦Name of Application used
 ◦Name of Database affected
 ◦Host where transaction originated
 ◦Login name of the user...."

http://www.isrcomputing.com/knowledge-base/information-security/150-how-to-enable-sql-2005-database-auditing.html

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

As there are different types of auditing & you haven't mentioned any specific so putting in following links for your reference:

How to: Configure Login Auditing

Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005

Hope that helps.

Answer 3

Hi anuragsh,

I want to trace auditing production changes such as modification, creation, deletion, etc. There is a database named "AXDB" that I need its auditing details. I am working with solution provided by Kalman. And will let you all know if everything works fine.

Answer 4

Hi Kalman,

Many thanks for the reply. Your suggested reply worked for me. With the stored procedure given in the article it created trace file for 3 times but now it does not creates further, no idea what is wrong. The SQL Mgmt Studio responds that "Command completed successfully" but the file is never created at the mentioned path in stored procedure.

Please advice.

Best Regards.

Answer 5

Hi,

For auditing of your Database and SQL Server objects, I believe that you can also try some third party tools also for this if still you are not able to solve in your problem. I had a similar problem where I used Lepide's tool to audit my database objects to know who did the changes in the objects, what changes are done, when are these changes done and where these changes are done.