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

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 2

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...