Who deleted my sql table rows?

Question

I have an SQL table, from which data is being deleted. Nobody knows how the data is being deleted. I added a trigger and know the time, however no jobs are running that would delete the data. I also added a trigger whenever rows are being deleted from this table. I am then inserting the deleted rows and the SYSTEM_USER to a log table, however that doesnt help much. Is there anything better I can do to know who and how the data gets deleted? Would it be possible to get the server id or something? Thanks for any advice.

Sorry: I am using SQL Server 2000.

*update 1: Its important to find out how the data gets deleted - preferably I would like to know the DTS package or SQL statement that is being executed.

Answer 1

Change all your passwords. Give as few people delete access as possible.

Answer 2

What recovery model is your database in? If it is full Redgate log Rescue is free and works against SQL2000 which might help you retrieve the deleted data. I'm not sure if there is anything in the logs that might help identify the source of the commands though.

Edit: The Overview Video does appear to show a user column.

Answer 3

If the recovery mode is set to "Full", you can check the logs.

Beyond that, remove any delete grants to the table. If it still happens, whomever is doing it has root/dbo access - so change the password...

Answer 4

Just a guess, but do you have delete cascades on one of the parent tables (those referenced by foreign keys). If so, when you delete the parent row the entries in the child table are also removed.

Answer 5

Try logging all transactions for the time being, even if if it hurts performance. MS offers a mssql profiler, including for express versions if needed. With it, you should be able to log transactions. As an alternative to profilers, you can use the trace_build stored procedure to dump activity into reference files, then just 'ctrl-f' for any instance of the word 'delete' or other similar keywords. For more info, see this SO page...

http://stackoverflow.com/questions/123781/logging-all-queries-on-a-sql-server-2008-express-database

Also, and this may sound stupid, but investigate the possibility that what you are seeing is not deletes. Instead, investigate if records are simply being 'updated', 'replaced if already exists', 'upserted', or whatever you like to call it. In Mysql, this is the 'INSERT ... ON DUPLICATE KEY UPDATE' statement. I'm not sure of the MSSQL variant.