V.Urgent Is there anything to help me find who connected and changed a table....?

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by Wyat » Sat, 23 Feb 2002 07:07:43



Can anyone help me solve this:

A transaction occurred on one of the important databases.75000 rows were
updated with one value in a field of one table.  It was almost a major
disaster.  As I began to investigate, it started to look like something
other than a system or programming fault.

It became top priority to find out who did this update (and to repair it).
Restoring and rolling forward was not an option as there were many
legitimate transactions since this one.

I used a tool called log explorer to find any clues.  I found out that the
update was done by 'dbo'.  This narrows it down to anyone in the group
called BuiltinAdmins - or possibly Global Admins.  I can use Log explorer to
look back and find out what changes were made to what tables, etc.  Is there
a system table that would tell me who connected to that database at that
time (I know the exact time of the transaction)?

I have looked at teh NT logs and they only tell me actual logins to that
box.  I believe this was done via Query Analyser.  Is there anywhere it
would be reported as to who was actually connecting to that database - or
hopefully who ran the query (I believe I may know exactly what the query
was)

OR

What if I restore to an exact point in time (when it happened) on a dummy
db.  Would I then be able to look at any system tables to see a snapshot of
who was connected, etc?

By the way, the backups and transaction log backups are on tape.  Does
anyone know any way of getting the log file off the tape (NSE Backup Exec)
without actually restoring it?

OR

Is there any way of restoring to a point in time and then replaying the
transactions and running profiler or something...?

Is there anything at all I can do?

Any help appreciated.

Please do not hesitate to send suggestions.  I am working on this right now
and really do need to sort it out.


 
 
 

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by Narayana Vyas Kondredd » Sat, 23 Feb 2002 07:50:40


Wyatt, Log Explorer recent version shows you the NT login name of the
connection that ran the transaction. That might help you zero-in on the
culprit.

Even if you do a point-in-time restore, you won't be able to get the
'connected logins' info, as that info is stored in the master database
(sysprocesses).

You should be able to generate an undo script using Log Explorer to undo
this transaction.
Good luck!
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Can anyone help me solve this:

A transaction occurred on one of the important databases.75000 rows were
updated with one value in a field of one table.  It was almost a major
disaster.  As I began to investigate, it started to look like something
other than a system or programming fault.

It became top priority to find out who did this update (and to repair it).
Restoring and rolling forward was not an option as there were many
legitimate transactions since this one.

I used a tool called log explorer to find any clues.  I found out that the
update was done by 'dbo'.  This narrows it down to anyone in the group
called BuiltinAdmins - or possibly Global Admins.  I can use Log explorer to
look back and find out what changes were made to what tables, etc.  Is there
a system table that would tell me who connected to that database at that
time (I know the exact time of the transaction)?

I have looked at teh NT logs and they only tell me actual logins to that
box.  I believe this was done via Query Analyser.  Is there anywhere it
would be reported as to who was actually connecting to that database - or
hopefully who ran the query (I believe I may know exactly what the query
was)

OR

What if I restore to an exact point in time (when it happened) on a dummy
db.  Would I then be able to look at any system tables to see a snapshot of
who was connected, etc?

By the way, the backups and transaction log backups are on tape.  Does
anyone know any way of getting the log file off the tape (NSE Backup Exec)
without actually restoring it?

OR

Is there any way of restoring to a point in time and then replaying the
transactions and running profiler or something...?

Is there anything at all I can do?

Any help appreciated.

Please do not hesitate to send suggestions.  I am working on this right now
and really do need to sort it out.



 
 
 

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by wbtaylo » Sun, 24 Feb 2002 00:42:22


I had the exact same thing happen. If you find out please
let me know. If I stumble across a solutions I will post
it to you.

Quote:>-----Original Message-----
>Can anyone help me solve this:

>A transaction occurred on one of the important

databases.75000 rows were
Quote:>updated with one value in a field of one table.  It was
almost a major
>disaster.  As I began to investigate, it started to look
like something
>other than a system or programming fault.

>It became top priority to find out who did this update
(and to repair it).
>Restoring and rolling forward was not an option as there
were many
>legitimate transactions since this one.

>I used a tool called log explorer to find any clues.  I
found out that the
>update was done by 'dbo'.  This narrows it down to anyone
in the group
>called BuiltinAdmins - or possibly Global Admins.  I can
use Log explorer to
>look back and find out what changes were made to what

tables, etc.  Is there

- Show quoted text -

Quote:>a system table that would tell me who connected to that
database at that
>time (I know the exact time of the transaction)?

>I have looked at teh NT logs and they only tell me actual
logins to that
>box.  I believe this was done via Query Analyser.  Is
there anywhere it
>would be reported as to who was actually connecting to
that database - or
>hopefully who ran the query (I believe I may know exactly
what the query
>was)

>OR

>What if I restore to an exact point in time (when it

happened) on a dummy

- Show quoted text -

>db.  Would I then be able to look at any system tables to
see a snapshot of
>who was connected, etc?

>By the way, the backups and transaction log backups are
on tape.  Does
>anyone know any way of getting the log file off the tape
(NSE Backup Exec)
>without actually restoring it?

>OR

>Is there any way of restoring to a point in time and then
replaying the
>transactions and running profiler or something...?

>Is there anything at all I can do?

>Any help appreciated.

>Please do not hesitate to send suggestions.  I am working
on this right now
>and really do need to sort it out.


>.

 
 
 

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by Kevi » Sun, 24 Feb 2002 00:58:14


To get the database up and running again, restore into a new db name, and
use DTS to merge the two tables (unaltered records from the production db,
correct records from restored db, and new records added in prod since
problem)

Kevin

> Can anyone help me solve this:

> A transaction occurred on one of the important databases.75000 rows were
> updated with one value in a field of one table.  It was almost a major
> disaster.  As I began to investigate, it started to look like something
> other than a system or programming fault.

> It became top priority to find out who did this update (and to repair it).
> Restoring and rolling forward was not an option as there were many
> legitimate transactions since this one.

> I used a tool called log explorer to find any clues.  I found out that the
> update was done by 'dbo'.  This narrows it down to anyone in the group
> called BuiltinAdmins - or possibly Global Admins.  I can use Log explorer
to
> look back and find out what changes were made to what tables, etc.  Is
there
> a system table that would tell me who connected to that database at that
> time (I know the exact time of the transaction)?

> I have looked at teh NT logs and they only tell me actual logins to that
> box.  I believe this was done via Query Analyser.  Is there anywhere it
> would be reported as to who was actually connecting to that database - or
> hopefully who ran the query (I believe I may know exactly what the query
> was)

> OR

> What if I restore to an exact point in time (when it happened) on a dummy
> db.  Would I then be able to look at any system tables to see a snapshot
of
> who was connected, etc?

> By the way, the backups and transaction log backups are on tape.  Does
> anyone know any way of getting the log file off the tape (NSE Backup Exec)
> without actually restoring it?

> OR

> Is there any way of restoring to a point in time and then replaying the
> transactions and running profiler or something...?

> Is there anything at all I can do?

> Any help appreciated.

> Please do not hesitate to send suggestions.  I am working on this right
now
> and really do need to sort it out.



 
 
 

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by Wyat » Tue, 26 Feb 2002 03:09:03


Thanks Guys,

I cleaned up the db no problem but still cannot be sure who did it :-(


> Can anyone help me solve this:

> A transaction occurred on one of the important databases.75000 rows were
> updated with one value in a field of one table.  It was almost a major
> disaster.  As I began to investigate, it started to look like something
> other than a system or programming fault.

> It became top priority to find out who did this update (and to repair it).
> Restoring and rolling forward was not an option as there were many
> legitimate transactions since this one.

> I used a tool called log explorer to find any clues.  I found out that the
> update was done by 'dbo'.  This narrows it down to anyone in the group
> called BuiltinAdmins - or possibly Global Admins.  I can use Log explorer
to
> look back and find out what changes were made to what tables, etc.  Is
there
> a system table that would tell me who connected to that database at that
> time (I know the exact time of the transaction)?

> I have looked at teh NT logs and they only tell me actual logins to that
> box.  I believe this was done via Query Analyser.  Is there anywhere it
> would be reported as to who was actually connecting to that database - or
> hopefully who ran the query (I believe I may know exactly what the query
> was)

> OR

> What if I restore to an exact point in time (when it happened) on a dummy
> db.  Would I then be able to look at any system tables to see a snapshot
of
> who was connected, etc?

> By the way, the backups and transaction log backups are on tape.  Does
> anyone know any way of getting the log file off the tape (NSE Backup Exec)
> without actually restoring it?

> OR

> Is there any way of restoring to a point in time and then replaying the
> transactions and running profiler or something...?

> Is there anything at all I can do?

> Any help appreciated.

> Please do not hesitate to send suggestions.  I am working on this right
now
> and really do need to sort it out.



 
 
 

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by wbtaylo » Fri, 01 Mar 2002 00:31:17


WHere can I get a copy of Log Explorer?

>-----Original Message-----
>Wyatt, Log Explorer recent version shows you the NT login
name of the
>connection that ran the transaction. That might help you
zero-in on the
>culprit.

>Even if you do a point-in-time restore, you won't be able
to get the
>'connected logins' info, as that info is stored in the
master database
>(sysprocesses).

>You should be able to generate an undo script using Log
Explorer to undo
>this transaction.
>Good luck!
>--
>HTH,
>Vyas, MVP (SQL Server)

>http://vyaskn.tripod.com/



>Can anyone help me solve this:

>A transaction occurred on one of the important

databases.75000 rows were

- Show quoted text -

Quote:>updated with one value in a field of one table.  It was
almost a major
>disaster.  As I began to investigate, it started to look
like something
>other than a system or programming fault.

>It became top priority to find out who did this update
(and to repair it).
>Restoring and rolling forward was not an option as there
were many
>legitimate transactions since this one.

>I used a tool called log explorer to find any clues.  I
found out that the
>update was done by 'dbo'.  This narrows it down to anyone
in the group
>called BuiltinAdmins - or possibly Global Admins.  I can
use Log explorer to
>look back and find out what changes were made to what

tables, etc.  Is there

- Show quoted text -

Quote:>a system table that would tell me who connected to that
database at that
>time (I know the exact time of the transaction)?

>I have looked at teh NT logs and they only tell me actual
logins to that
>box.  I believe this was done via Query Analyser.  Is
there anywhere it
>would be reported as to who was actually connecting to
that database - or
>hopefully who ran the query (I believe I may know exactly
what the query
>was)

>OR

>What if I restore to an exact point in time (when it

happened) on a dummy

- Show quoted text -

>db.  Would I then be able to look at any system tables to
see a snapshot of
>who was connected, etc?

>By the way, the backups and transaction log backups are
on tape.  Does
>anyone know any way of getting the log file off the tape
(NSE Backup Exec)
>without actually restoring it?

>OR

>Is there any way of restoring to a point in time and then
replaying the
>transactions and running profiler or something...?

>Is there anything at all I can do?

>Any help appreciated.

>Please do not hesitate to send suggestions.  I am working
on this right now
>and really do need to sort it out.


>.

 
 
 

V.Urgent Is there anything to help me find who connected and changed a table....?

Post by Narayana Vyas Kondredd » Fri, 01 Mar 2002 00:42:50


go to http://www.lumigent.com and download the evaluation copy.
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


> WHere can I get a copy of Log Explorer?

> >-----Original Message-----
> >Wyatt, Log Explorer recent version shows you the NT login
> name of the
> >connection that ran the transaction. That might help you
> zero-in on the
> >culprit.

> >Even if you do a point-in-time restore, you won't be able
> to get the
> >'connected logins' info, as that info is stored in the
> master database
> >(sysprocesses).

> >You should be able to generate an undo script using Log
> Explorer to undo
> >this transaction.
> >Good luck!
> >--
> >HTH,
> >Vyas, MVP (SQL Server)

> >http://vyaskn.tripod.com/



> >Can anyone help me solve this:

> >A transaction occurred on one of the important
> databases.75000 rows were
> >updated with one value in a field of one table.  It was
> almost a major
> >disaster.  As I began to investigate, it started to look
> like something
> >other than a system or programming fault.

> >It became top priority to find out who did this update
> (and to repair it).
> >Restoring and rolling forward was not an option as there
> were many
> >legitimate transactions since this one.

> >I used a tool called log explorer to find any clues.  I
> found out that the
> >update was done by 'dbo'.  This narrows it down to anyone
> in the group
> >called BuiltinAdmins - or possibly Global Admins.  I can
> use Log explorer to
> >look back and find out what changes were made to what
> tables, etc.  Is there
> >a system table that would tell me who connected to that
> database at that
> >time (I know the exact time of the transaction)?

> >I have looked at teh NT logs and they only tell me actual
> logins to that
> >box.  I believe this was done via Query Analyser.  Is
> there anywhere it
> >would be reported as to who was actually connecting to
> that database - or
> >hopefully who ran the query (I believe I may know exactly
> what the query
> >was)

> >OR

> >What if I restore to an exact point in time (when it
> happened) on a dummy
> >db.  Would I then be able to look at any system tables to
> see a snapshot of
> >who was connected, etc?

> >By the way, the backups and transaction log backups are
> on tape.  Does
> >anyone know any way of getting the log file off the tape
> (NSE Backup Exec)
> >without actually restoring it?

> >OR

> >Is there any way of restoring to a point in time and then
> replaying the
> >transactions and running profiler or something...?

> >Is there anything at all I can do?

> >Any help appreciated.

> >Please do not hesitate to send suggestions.  I am working
> on this right now
> >and really do need to sort it out.


> >.