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
  • Which backup strategy is used? Do you have full transaction log backups? – Heinzi Feb 06 '17 at 19:07
  • 1
    If it's a deletion, you can just make sure that the data isn't there anymore. That won't tell you when it was deleted or who did it though. – RBarryYoung Feb 06 '17 at 19:08
  • @RBarryYoung yes, i´ve already figured out that it is not so much that can be done just asking to see if there is something that i´m missing that can be used –  Feb 06 '17 at 19:10
  • @Heinzi they do have transaction logs backups. The objective here anyway would be to know WHO deleted the info. Restoring in this case is not as much as important as knowing who was the person that deleted that info –  Feb 06 '17 at 19:12
  • 1
    If you have a DataBase with Recovery model set Full, Use Apex log software. It is useful. – Jota Pardo Feb 06 '17 at 19:18
  • @JotaPardo did not know the tool. I´m gonna take a look at it –  Feb 06 '17 at 19:32
  • See How to recover deleted data from SQL Server and this answer. Dont run profiler instead look into sql server audit or server side trace. – Kin Shah Feb 06 '17 at 19:36
  • 2
    You should set up your own auditing through triggers, Profiler should not be used for auditing and should generally not be used in Production at all. Our audit tables capture the exact changes (old and new values) and the login of who did it, the application the change came from, and the datetime of the change. – HLGEM Feb 06 '17 at 19:55
  • @HLGEM thanks for advice I also do auditing by triggers on the databases I own and design. Anyway I cannot touch the database schema since it is from another software manufacturer. I´ll give a try to the tools that were exposed in this thread – NicoRiff Feb 06 '17 at 19:59

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
  • 42,952
  • 47
  • 220
  • 375
  • I ´m gonna give it a try. Actually all the applications that are targetting to database are using ODBC connections with SQL login, so as you say it will be hard to catch. In the other hand if the deletion was using an external tool we might have some probabilitties on knowing what happened. – NicoRiff Feb 06 '17 at 19:48