How to know who deleted / when deleted data in a table in sql server 2008 r2 please

Question

How to know who deleted / when deleted data in a table in sql server 2008 r2 please

asita

SSCarpal Tunnel

Points: 4144
More actions
July 9, 2013 at 9:47 am

#296220

Hello All
I am using SQL server 2008 R2, i have a table called dbo.JJ_CARSDATA_Header, some how this table data is getting deleted,
is there anyway how to know , when was last deleted/last modified/ who did it by query wise
please give me any advise
please...
Thank you
Dhani

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

asita (7/9/2013)
Hello All
I am using SQL server 2008 R2, i have a table called dbo.JJ_CARSDATA_Header, some how this table data is getting deleted,
is there anyway how to know , when was last deleted/last modified/ who did it by query wise
please give me any advise
please...
Thank you
Dhani

If it is recent enough you might be able to see when it was done in the default trace. Unless you have some logging in place there is no way to know who did it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's two ways to get to the default trace:

one via a query:

--SELECT * from sys.traces

declare @TraceIDToReview int

declare @path varchar(255)

SET @TraceIDToReview = 1 --this is the trace you want to review!

SELECT @path = path from sys.traces WHERE id = @TraceIDToReview

SELECT

TE.name As EventClassDescrip,

v.subclass_name As EventSubClassDescrip,

T.*

FROM ::fn_trace_gettable(@path, default) T

LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

LEFT OUTER JOIN sys.trace_subclass_values V

ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value

and the other way via the reports in the SSMS GUI:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Deleted data is not recorded in the default trace.

You need triggers, change data capture, change tracking or SQL Audit set up before the deletes occurred to have any record of who deleted the data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Hello All,

thank you for your inputs, i appreciate your help,

unluckly i dont have any audit/log enabled

now onwards, how can i make it auditable (with out my dba interfere), what could be the best suggestion, is the trigger

please advise me

thank you in advance

dhani

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

asita (7/9/2013)
Hello All,
thank you for your inputs, i appreciate your help,
unluckly i dont have any audit/log enabled
now onwards, how can i make it auditable (with out my dba interfere), what could be the best suggestion, is the trigger
please advise me
thank you in advance
dhani

Why do you want to go around your dba to set up something like this? Your dba should have the ability to help you diagnose the issue and correct it. If you just want to identify what is causing the data to be deleted - your dba could setup a server-side trace to capture any statements that hit that table.

If you want to audit all access, your dba can help you setup auditing (change data capture) - or other options.

There are a lot of options available and your dba can help you identify the best solution for your environment. That is his/her job and you should use them for this. Just my opinion....

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 6

Hi,

Please check the below link. It is showing how to get the user details who deleted the data from Transaction Log.

http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/

_____________________________________________
One ounce of practice is more important than tonnes of dreams