Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
1  
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 0:52
    
No these are DBA's. With much power comes great resposibility...and hopefully an audit trail. –  Ducain Mar 17 '11 at 15:20
add comment

4 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
I'm going to look into SQL Profiler. –  Ducain Mar 17 '11 at 15:43
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.