Unknown job delete records on table sql server

Question

I have a unknown job that is deleting records from a table on SQL server 2005. We can't find it yet. Any of you have a clue of how can we know wish stored procedure or job is deleting this records in a X table?. Is there any way to know or record on database that can tell us specifically, "this sp or job delete records from that table in this date and hour"?.

Answer 1

2 different approaches:

1. Add a trigger (For Delete) to gather the name of the logged-in user: suser_sname() or the running app. You will need to create a table to store this information (commonly known as an Audit table).
2. Use the Profiler to watch all traffic on the database.

The trigger approach is better and you can even block/reject/abort delete commands on a per-table basis.