Tags: backup, deleted, luckily, production, programming, records, restore, server, sql, table, tlogs

Who deleted records??

On Programmer » SQL

6,774 words with 4 Comments; publish: Thu, 29 May 2008 02:23:00 GMT; (20091.80, « »)

Hi,

How do I find out who deleted the records from a table in my production

server?

Luckily, I have tlogs backup in place so that I can restore to point in

time. But I want to find the culprit who did it?

Is there some auditing done within SQL Server 2000 or any system tables

which gives this info that who deleted the records from table? It was a

very important table in our ERP system which records minute-to-minute

changes in inventory transactions which take place every second..

Please let me know if any tips or any hack available.

Regards,

Dilip

All Comments

Leave a comment...

  • 4 Comments
    • Hi

      Write a trigger for DELETE on this table an insert a data were deleted into

      an AuditTable along with HOST_NAME() function to identify by whom it was

      deleted.

      Or

      Deny DELETE permission on the table

      <dilipn123.sql.todaysummary.com.gmail.com> wrote in message

      news:1123760849.259318.305810.sql.todaysummary.com.g14g2000cwa.googlegroups.com...

      > Hi,

      > How do I find out who deleted the records from a table in my production

      > server?

      > Luckily, I have tlogs backup in place so that I can restore to point in

      > time. But I want to find the culprit who did it?

      > Is there some auditing done within SQL Server 2000 or any system tables

      > which gives this info that who deleted the records from table? It was a

      > very important table in our ERP system which records minute-to-minute

      > changes in inventory transactions which take place every second..

      > Please let me know if any tips or any hack available.

      > Regards,

      > Dilip

      >

      #1; Thu, 29 May 2008 02:24:00 GMT
    • Hi

      You need to use 3rd party tools:

      LogPI http://www.logpi.com

      Log Explorer http://www.lumigent.com/products/le_sql.html

      Regards

      --

      Mike Epprecht, Microsoft SQL Server MVP

      Zurich, Switzerland

      MVP Program: http://www.microsoft.com/mvp

      Blog: http://www.msmvps.com/epprecht/

      "dilipn123.sql.todaysummary.com.gmail.com" wrote:

      > Hi,

      > How do I find out who deleted the records from a table in my production

      > server?

      > Luckily, I have tlogs backup in place so that I can restore to point in

      > time. But I want to find the culprit who did it?

      > Is there some auditing done within SQL Server 2000 or any system tables

      > which gives this info that who deleted the records from table? It was a

      > very important table in our ERP system which records minute-to-minute

      > changes in inventory transactions which take place every second..

      > Please let me know if any tips or any hack available.

      > Regards,

      > Dilip

      >

      #2; Thu, 29 May 2008 02:25:00 GMT
    • Uri,

      The action has already occured so now writing a trigger wont help..yes

      in future what you suggest will help..but im dealing with a past

      situation..so any pointers on this'

      Regards,

      Dilip

      Mike Epprecht (SQL MVP) wrote:

      > Hi

      > You need to use 3rd party tools:

      > LogPI http://www.logpi.com

      > Log Explorer http://www.lumigent.com/products/le_sql.html

      > Regards

      > --

      > Mike Epprecht, Microsoft SQL Server MVP

      > Zurich, Switzerland

      > MVP Program: http://www.microsoft.com/mvp

      > Blog: http://www.msmvps.com/epprecht/

      >

      > "dilipn123.sql.todaysummary.com.gmail.com" wrote:

      > > Hi,

      > >

      > > How do I find out who deleted the records from a table in my production

      > > server?

      > >

      > > Luckily, I have tlogs backup in place so that I can restore to point in

      > > time. But I want to find the culprit who did it?

      > >

      > > Is there some auditing done within SQL Server 2000 or any system tables

      > > which gives this info that who deleted the records from table? It was a

      > > very important table in our ERP system which records minute-to-minute

      > > changes in inventory transactions which take place every second..

      > >

      > > Please let me know if any tips or any hack available.

      > >

      > > Regards,

      > > Dilip

      > >

      > >

      #3; Thu, 29 May 2008 02:26:00 GMT
    • Hi

      No you can't do anything on this.

      Just as one of the guys mentioned you can use a third party solution to

      analyze the log this is the only available solution for you.

      Nader

      "dilipn123.sql.todaysummary.com.gmail.com" wrote:

      > Uri,

      > The action has already occured so now writing a trigger wont help..yes

      > in future what you suggest will help..but im dealing with a past

      > situation..so any pointers on this'

      > Regards,

      > Dilip

      > Mike Epprecht (SQL MVP) wrote:

      > > Hi

      > >

      > > You need to use 3rd party tools:

      > >

      > > LogPI http://www.logpi.com

      > > Log Explorer http://www.lumigent.com/products/le_sql.html

      > >

      > > Regards

      > > --

      > > Mike Epprecht, Microsoft SQL Server MVP

      > > Zurich, Switzerland

      > >

      > > MVP Program: http://www.microsoft.com/mvp

      > >

      > > Blog: http://www.msmvps.com/epprecht/

      > >

      > >

      > >

      > > "dilipn123.sql.todaysummary.com.gmail.com" wrote:

      > >

      > > > Hi,

      > > >

      > > > How do I find out who deleted the records from a table in my production

      > > > server?

      > > >

      > > > Luckily, I have tlogs backup in place so that I can restore to point in

      > > > time. But I want to find the culprit who did it?

      > > >

      > > > Is there some auditing done within SQL Server 2000 or any system tables

      > > > which gives this info that who deleted the records from table? It was a

      > > > very important table in our ERP system which records minute-to-minute

      > > > changes in inventory transactions which take place every second..

      > > >

      > > > Please let me know if any tips or any hack available.

      > > >

      > > > Regards,

      > > > Dilip

      > > >

      > > >

      >

      #4; Thu, 29 May 2008 02:27:00 GMT