You could setup a profiler session to trap the queries from these users.
I've done this many times to get get direct evidence of what queries are
being run and when.

Jim

"C Kirby" <ckirby@mindspring.com> wrote in message
news:5kb5tv8nuo2prnpq5ju73o08p0mq587tgr@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?

I'm trying to set up the profiler to watch the events, but the filter
doesn't seem to be working like it should.
I'm tracing Stored Procedures, Transactions, and TSQL and I'm
filtering it for the particular database I'm working on, and the
particular objectname (tblArTransDetail in this case), but the trace
is pulling in a lot of events that are not directly tied to this table
(as far as I can tell). Does the filter let events that are
marginally related to the object pass through or am I not setting it
up correctly?


On Mon, 8 Dec 2003 10:37:01 -0800, "J Young" <thorium48@hotmail.com>
wrote:

>You could setup a profiler session to trap the queries from these users.
>I've done this many times to get get direct evidence of what queries are
>being run and when.
>
>Jim
>
>"C Kirby" <ckirby@mindspring.com> wrote in message
>news:5kb5tv8nuo2prnpq5ju73o08p0mq587tgr@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?
>

[posted and mailed, please reply in news]

C Kirby (ckirby@mindspring.com) writes:
> I'm trying to set up the profiler to watch the events, but the filter
> doesn't seem to be working like it should.
> I'm tracing Stored Procedures, Transactions, and TSQL and I'm
> filtering it for the particular database I'm working on, and the
> particular objectname (tblArTransDetail in this case), but the trace
> is pulling in a lot of events that are not directly tied to this table
> (as far as I can tell). Does the filter let events that are
> marginally related to the object pass through or am I not setting it
> up correctly?

One problem with the filtering, is that if you filter for, say,
"hostname" equal to "NISSE", you will get all events with this
value in hostname - and you will get all events with no value in
hostname at all. The only way to avoid this, is to filter on a column
which is filled in for all events you have selected.

I would suggest, though, that adding a trigger that audit events on
the table into a log table is probably faster way to find the culprit.


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

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

Thanks for all the tips and suggestions!! Fortunately, the answer
ended up being *mch* simpler to find. I'm almost embarassed to admit
it, but it was my screw-up the whole time. I had copied an 'on
Delete' trigger from one table to another, and didn't fully modify it
for the new purpose, so it was doing it's old action instead of the
'new' action. I looked at all the dependecies for the table that was
getting records dropped, and this trigger showed up!! I fixed it, so
I think the problem is gone.

thanks again for all the help!


On Thu, 11 Dec 2003 23:45:15 +0000 (UTC), Erland Sommarskog
<sommar@algonet.se> wrote:

>[posted and mailed, please reply in news]
>
>C Kirby (ckirby@mindspring.com) writes:
>> I'm trying to set up the profiler to watch the events, but the filter
>> doesn't seem to be working like it should.
>> I'm tracing Stored Procedures, Transactions, and TSQL and I'm
>> filtering it for the particular database I'm working on, and the
>> particular objectname (tblArTransDetail in this case), but the trace
>> is pulling in a lot of events that are not directly tied to this table
>> (as far as I can tell). Does the filter let events that are
>> marginally related to the object pass through or am I not setting it
>> up correctly?
>
>One problem with the filtering, is that if you filter for, say,
>"hostname" equal to "NISSE", you will get all events with this
>value in hostname - and you will get all events with no value in
>hostname at all. The only way to avoid this, is to filter on a column
>which is filled in for all events you have selected.
>
>I would suggest, though, that adding a trigger that audit events on
>the table into a log table is probably faster way to find the culprit.
>