2

A client of ours has doubts that a user erased certain information from the database they are using. I know that the database can be audited by having SQL Server Profiler enabled, but in this case, there wasn't any tool enabled to do auditing.

My question is: is there a way to know if some user has committed a transaction on the database without having profiler enabled? Is there any forensic task that can be done to accomplish this?

Thanks.

NicoRiff
  • 135
  • 1
  • 1
  • 6

1 Answers1

4

If you have the full and transaction log backups, you can use a log reading tool like Quest Litespeed or ApexSQL Log Reader. (Disclaimer: I used to work for Quest.) Those read the full backup, then the logs, in order to build a chain of what happened. You can search for transactions by syntax, table name, etc, and generate undo scripts.

Here's the catch, though: if it was a shared login, like a SQL login where multiple people knew the password, you're going to have a tough time pinning down who did it.

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390