Row deletion

Hi!
I have a table with the history of blocks for few months. It is
automaticaly filled as new blocks appear. Recently I found that
someone is deleting data from two columns. /* he updates two columns
to NULL value */
It is done by sime kind of sheduled job or trigger, because the data
is deleted right away. The guy who did this is most probably from
development and has sa rights on many servers.
Can I get some step by step hints how to track this?
My best wish is to figure out where this came from...
Perhaps he added a spare step for a job, or created a separate job on
another server, etc.
Addin an 'instead of update trigger' is not an option, cause I, myself
do some updates on this table via job. Another reason is that it is
easy to disable all triggers before the update.
Thanks.Start a SQL Profiler trace and leave it running overnight. In the morning
you should have captured the offending statement and see where it came from.
--
HTH
Ryan Waight, MCDBA, MCSE
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309290649.1a94a0cc@.posting.google.com...
> Hi!
> I have a table with the history of blocks for few months. It is
> automaticaly filled as new blocks appear. Recently I found that
> someone is deleting data from two columns. /* he updates two columns
> to NULL value */
> It is done by sime kind of sheduled job or trigger, because the data
> is deleted right away. The guy who did this is most probably from
> development and has sa rights on many servers.
> Can I get some step by step hints how to track this?
> My best wish is to figure out where this came from...
> Perhaps he added a spare step for a job, or created a separate job on
> another server, etc.
> Addin an 'instead of update trigger' is not an option, cause I, myself
> do some updates on this table via job. Another reason is that it is
> easy to disable all triggers before the update.
> Thanks.|||In this case SQL Server profiler may help you to some good
level. Through which you can start a new trace.
Add an event for "stmtcompleted" under TSQL event class.
This will capture SQL statements that are getting
executed.
It will give you details about
the "applicationname"/"ntusername"/"loginname" that is
executing the statement.
- Vishal
>--Original Message--
>Hi!
>I have a table with the history of blocks for few months.
It is
>automaticaly filled as new blocks appear. Recently I
found that
>someone is deleting data from two columns. /* he updates
two columns
>to NULL value */
>It is done by sime kind of sheduled job or trigger,
because the data
>is deleted right away. The guy who did this is most
probably from
>development and has sa rights on many servers.
>Can I get some step by step hints how to track this?
>My best wish is to figure out where this came from...
>Perhaps he added a spare step for a job, or created a
separate job on
>another server, etc.
>Addin an 'instead of update trigger' is not an option,
cause I, myself
>do some updates on this table via job. Another reason is
that it is
>easy to disable all triggers before the update.
>Thanks.
>.
>|||If it were me, I would search every one of my stored procs and triggers for
an update statement that updates the table in question. For each stored
proc found, I would determine what other objects (or jobs) call this stored
proc. This should make it somewhat easy to determine how this is happening.
BTW, Largo SQL Tools makes this quite easy to do.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309290649.1a94a0cc@.posting.google.com...
> Hi!
> I have a table with the history of blocks for few months. It is
> automaticaly filled as new blocks appear. Recently I found that
> someone is deleting data from two columns. /* he updates two columns
> to NULL value */
> It is done by sime kind of sheduled job or trigger, because the data
> is deleted right away. The guy who did this is most probably from
> development and has sa rights on many servers.
> Can I get some step by step hints how to track this?
> My best wish is to figure out where this came from...
> Perhaps he added a spare step for a job, or created a separate job on
> another server, etc.
> Addin an 'instead of update trigger' is not an option, cause I, myself
> do some updates on this table via job. Another reason is that it is
> easy to disable all triggers before the update.
> Thanks.