Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find who/process that deleted records Expand / Collapse
Author
Message
Posted Thursday, January 17, 2008 5:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
Is it possible to find who deleted records from a table? The DB is set for Full recovery model. This is on a SQL 2005 Server w/SP2. I don't know if it is a person or a process somewhere. It's not at any predictable time this happens. It's pretty random. As of now, It happened 2 to 6 hrs ago.

Thanks
Post #444680
Posted Thursday, January 17, 2008 6:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:51 PM
Points: 908, Visits: 2,803
Retroactively, probably not. There's a very slim chance that you might possibly find a shred of evidence in the transaction logs.

Proactively:

set up a Profiler trace or an On Delete trigger (best if there's a specific table in question).
Post #444686
Posted Friday, January 18, 2008 8:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 4,134, Visits: 5,853
Yes, there is. Buy a copy of ApexSQL Log from ApexSQL.com. It is my VERY strong opinion that every production sql server environment should have the functionality this product provides on hand 24/7.


DISCLAIMER: I have a very close relationship with ApexSQL. I use and recommend their products to my clients. If you care to you can get a discount (and I can get a small remuneration) if you tell them TheSQLGuru sent you.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #444911
Posted Friday, January 18, 2008 9:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
There's a few other log products (Lumigent, LogPI), but Apex is probably the cheapest and will help you find this out if you have the logs or backups.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #444977
Posted Friday, January 18, 2008 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 PM
Points: 7,090, Visits: 14,715
This of course presumes that Windows auth is in place (or something to identify the user), right? won't help much if everyone is logging in under the same account, would it?


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #444985
Posted Friday, January 18, 2008 10:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 4,134, Visits: 5,853
By examining the transaction of concern (and perhaps some of those around it) it could still be possible to determine if it is a process or a person, even if everyone/thing logs in as SA.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #445011
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse