Using ::fn_dblog() to find who deleted the rows in a table.

Question

Post reply

Using ::fn_dblog() to find who deleted the rows in a table.

Viewing 4 posts - 16 through 18 (of 18 total)

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

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

I am aware of that.

However you can deduce who is doing what based on the entries in the default trace.

satyam9373 SSC Rookie Points: 25 More actions · Answer 2

use tempdb

go

SELECT

[Current LSN],

[Operation],

[Transaction ID],

[Description], SPID,[Begin Time], [Transaction SID],

name 'LoginName'

FROM fn_dblog (NULL, NULL),

(select sid,name from sys.syslogins) sl

where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid

my be it help's

Regards,

Satyam

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

arnipetursson (12/28/2012)
I am aware of that.
However you can deduce who is doing what based on the entries in the default trace.

How? How can you deduce who did a simple delete on a table from anything that appears in the default trace? I'm asking not as a challenge... I'm asking because, if you've actually been able to pull that off, I'd REALLY like to know because it would be incredibly useful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arnipetursson SSCertifiable Points: 6557 More actions · Answer 4

I have been able to deduce from seeing other activity by logins with sa privileges around the time of a given event.

E.g. tempdb object creation or worse yet sort warnings.

All it tells you is that a given login from a given server was active at a certain time.

Presumably a small list of logins has the ability to delete data.

I have found the culprit causing slowness at a given time, by finding sort warnings related to certain sessions.

All I am saying is that you can come up with a list of people to ask if you get lucky.