Suspect someone is deleting records from my SQL Server '05 DB - any way to check?

Question

Hey all,

I think someone with shared access to my SQL Server '05 DB is deleting records from a table in a DB for their own reasons.

Is there any audit table I can check to see manual delete queries which may have been run on the DB in the last X number of days?

Thanks for your help.

Ed

Answer 1

SQL Server Profiler is probably the easiest way to do this. You can set it to dump all executed queries to a table in the database, or to a file which might be more suitable in your case. You can also set a filter to capture just the queries you're interested in, or the log files become huge.

Answer 2

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Answer 3

I'd add an on delete trigger to the table in question. That would allow you to keep an exact log of deleted records (ie, if on your trigger you insert into another table, etc)

Answer 4

May want to consider using a trigger temporarily.

Here's an example.

Answer 5

Unless you've set things up beforehand (via triggers, running Profiler traces, or the like) no, there is no simple native way to "pull out" commands that have been run against a SQL Server database.

@David's idea of querying the procedure cache is one possibility, but would only work if the execution plan(s) are still in memory.

There are third-party transaction log readers available. They could be used to read the contents of the transaction log, but again that only helps if the data/commands are still in there, and after "X days" that seems unlikely.

Another work-around would depend on backups.

• Restore a copmlete backup from before your problem time, and compare and contrast with the current version. This would show if data has been deleted, but not how.
• If you are in Full backup mode and you have transaction log backups, you can perform various types of incremental restores and actually observer the deletions happening (if they are), but this would probably require a lot of point-in-time recoveries and would be very time intensive.