Records getting deleted mysteriously..

I'm running a DB using MSDE (2000) that is interfaced by 2 different
ades running on PCs with Access 2000 Runtime. One of the ADEs is a
package accounting system that is very solid and stable, the other is
a custom application that I wrote (much less solid and stable). The
custom app only deals with a select few tables in the database, and
the table in question is not one of those.
With alarming regularity(daily), records are getting deleted out of a
particular table. I've set up a couple of dummy records in the table
and put a delete trigger on the table that creates record in a 'log'
table that tells me the user and the time that the records are
deleted.
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?Hi

Check out any stored procedures to see if the they will delete them as the
ownership chain may allow them to access the table. You may also want to
check for any FKs with cascading deletes.

Usually I would profile this to get an overall view of what activity is
occuring.

John

"C Kirby" <ckirby@.mindspring.com> wrote in message
news:rub5tv0fnkodkf4d4qjtnl2rj1ahbv6976@.4ax.com...
> I'm running a DB using MSDE (2000) that is interfaced by 2 different
> ades running on PCs with Access 2000 Runtime. One of the ADEs is a
> package accounting system that is very solid and stable, the other is
> a custom application that I wrote (much less solid and stable). The
> custom app only deals with a select few tables in the database, and
> the table in question is not one of those.
> With alarming regularity(daily), records are getting deleted out of a
> particular table. I've set up a couple of dummy records in the table
> and put a delete trigger on the table that creates record in a 'log'
> table that tells me the user and the time that the records are
> deleted.
> 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?|||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/techin.../2000/books.asp