How did my database security fail?

Question

In several of my tables in my SQL Server 2005 database, all my data has been deleted. Is there anyway to get the SQL Server log of all the statements that were executed on the last day? I am trying to find out if someone did this in an accident, is there a vulnerability in my web application or the actual database has been compromised.

Answer 1

See the following: there are several programs that will allow you to read the magazine.

http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html

One of the Red Gate is called SQL Rescue and looks good.

Answer 2

You are looking for a transaction log. Depending on how and if it is configured, you will be able to see what has been done. There is information on this at http://www.databasedesign-resource.com/sql-server-transaction-log.html . With that in mind, I'm sure you can also use Google as your best resource.

Answer 3

You can also try running the DBCC LOG command (database, 3). It will output the data that is in the transaction log.

Answer 4

You can try a log recovery tool like Log Rescue

I would also consider your own audit.

Log Rescue does not support SQL 2005, so you can try Apex SQL Log

Answer 5

There are applications that you can buy that can convert a backup of the transaction log to the actual statements that were running. You may be able to find a trial version of some of them, unfortunately, I can not recommend any specific option.

Answer 6

Something else you need to keep in mind: if a hacker manages to get enough access to clean some tables, they have a good chance of getting enough access to their log files.

Answer 7

Make a backup of the transaction log in SQL Server, download the trial version of TOAD for SQL Server where you can import your Transactionlog backup.

And if you want, you can also create INSERT Scripts of DELETED records. But I do not know if there are any restrictions in the trial version of TOAD.