Best practices for SQL Server audit trail

Question

I'm facing a situation today where it would be very beneficial to me and my company if we knew who had logged into SQL and performed some deletions.

We have a situation where at least 2 (sometimes 3) people login to SQL using SQL Server Management Studio, and perform various functions.

What we need is an audit trail. If someone deletes records (mistakenly or otherwise), I'd like to know what was done.

Is there any way to make this happen?

Is it possible to keep these people from making their own SQL connections and require that they go through the application(s)? Or is it the DBAs themselves that have done this? — mfinni, Mar 17 '11 at 00:52
No these are DBA's. With much power comes great resposibility...and hopefully an audit trail. — Ducain, Mar 17 '11 at 15:20

score 2 · Accepted Answer · answered Mar 17 '11 at 08:32

There's a few difference tools you can use. If you have SQL Server 2008 or higher, there is built in auditing which can capture this information.

Failing that you can capture the information using either SQL Profiler, or a server side trace.

If your database is in full recovery you can dig through the transaction log using Log Explorer, or LiteSpeed for SQL Server. There are some undocumented commands which can be used if you want to write your own stuff.

I'm going to look into SQL Profiler. – Ducain Mar 17 '11 at 15:43 — Ducain, Mar 17 '11 at 15:43

score 2 · Answer 2 · answered Nov 18 '13 at 19:26

You can set up DML triggers, use the database transaction log to read transaction (DELETE) information, use native SQL Server features:

SQL Server Change Tracking
SQL Server Change Data Capture
SQL Server Auditing

or use 3rd party tools

ApexSQL has two auditing tools - ApexSQL Log and ApexSQL Audit. There are certain advantages to using one over the other, e.g:

The time period needed for auditing – whether it is prior to the installation of the tool or afterwards
The range of operations which can be audited
The automation, how audited information is stored and how it can be queried afterwards

Disclaimer: I work as a Product Support Engineer at ApexSQL

score 1 · Answer 3 · answered Mar 17 '11 at 00:10

1

For this kind of thing we have Idera Compliance Manager although I'm sure there are other vendors with similar products.

answered Mar 17 '11 at 00:10

Ian Chamberland

680
4
8

score 0 · Answer 4 · answered Mar 18 '11 at 16:13

As @mrdenny said there is a built in functionality that is introduced in 2008 for auditing. You can start with this and this articles.

Also you can check out this white paper:Auditing in SQL Server 2008 and Understanding SQL Server Audit

Keep in mind SQL profiler has big overhead associated with it.

Best practices for SQL Server audit trail

4 Answers4