C Kirby (ckirby@mindspring.com) writes:
> The deletion (all records in the table) always occurs during business
> hours (never over the weekend or at night) and the user responsible
> varies among 3 or 4 different users. 2 of those users don't even have
> rights to that table, so I'm really confused how those logins could
> cause a delete on the table they don't have access to!??!
> As far as I can tell, this is only happening to this particular table
> ( I hope!).
> Is there a way that I can get more information on the process or
> machine or anything else that is behind the deletion?

In addition to John's suggestion, here are a few more tips of what you
could put in the log table:

o The value of @@nestlevel. If the value is 1, the trigger was fired from
an explicit DELETE statement. If the value is 2, the trigger may have
been fired from a direct DELETE statement in a stored procedure, or
from another trigger.
o INSERT #tbl EXEC('DBCC INPUTBUFFER (' + ltrim(str(@@spid)) +
') WITH TABLE_RESULTS')
This will give you the command the user/application submitted. Look
up DBCC INPUTBUFFER in Books Online for details on the result set.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp