how to find the users who deleted the records from the sql table

Question

how to find the users who deleted the records from the sql table

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
November 16, 2016 at 6:28 am

#332211

Hi Sir,
how to find the users who deleted the records from the sql table?
do we have SQL script for this to get such type of information?
Please assist me.
Kiran

Viewing 11 posts - 1 through 10 (of 10 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

kiran.rajenimbalkar (11/16/2016)
Hi Sir,
how to find the users who deleted the records from the sql table?
do we have SQL script for this to get such type of information?
Please assist me.
Kiran

Unless there is some sort of custom trigger on the table, which runs on deletion and records the information, there is no inbuilt way of doing this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 2

kiran.rajenimbalkar

SSCrazy

Points: 2603

November 16, 2016 at 6:45 am

#1912769

Thanks Sir 🙂

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 3

Hi Sir,

I found one of the solution when i did r&d,

SELECT Operation,[Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]

FROM fn_dblog(NULL, NULL)

WHERE [Transaction ID] = '0000:535604c2' --(Use your Transaction ID)

AND [Operation] = 'LOP_BEGIN_XACT' --(Use LOP_DELETE_ROWS insted of LOP_BEGIN_XACT)

---Using Transaction ID , we can get exact user name as

Select SUSER_SNAME(0x88602B022114CD418E72040E910D4DEA)

what is your thoughts on this?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

kiran.rajenimbalkar (11/16/2016)
Hi Sir,
I found one of the solution when i did r&d,
SELECT Operation,[Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:535604c2' --(Use your Transaction ID)
AND [Operation] = 'LOP_BEGIN_XACT' --(Use LOP_DELETE_ROWS insted of LOP_BEGIN_XACT)
---Using Transaction ID , we can get exact user name as
Select SUSER_SNAME(0x88602B022114CD418E72040E910D4DEA)
what is your thoughts on this?

First of all, thank you for introducing me to a function I knew nothing about.

It follows, logically, that I have very few thoughts on the matter 🙂

The one thought I do have is that I have no idea how you would be able to work out which TransactionIds are the ones you are interested in. I think we need the input of others ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

That only works if the log records are still in the transaction log (which means you need to be doing the investigation very soon after the delete; minutes, not days), and the username is only useful if your users each have a unique login into the DB. If someone logged in as 'sa' and ran the delete, the only thing you'll get from the log is 'sa'.

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

GilaMonster (11/16/2016)
That only works if the log records are still in the transaction log (which means you need to be doing the investigation very soon after the delete; minutes, not days), and the username is only useful if your users each have a unique login into the DB. If someone logged in as 'sa' and ran the delete, the only thing you'll get from the log is 'sa'.

Thanks for weighing in, Gail.

Is it possible to return the underlying T-SQL which relates to a particular transaction Id?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 7

kiran.rajenimbalkar

SSCrazy

Points: 2603

November 16, 2016 at 7:28 am

#1912785

Thanks Sir for the update on this 🙂

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

Phil Parkin (11/16/2016)
Is it possible to return the underlying T-SQL which relates to a particular transaction Id?

Possibly via the sys.dm_tran_active_transactions DMV, providing the transaction is still active, but in general finding something in the log requires a good understanding of the log's architecture and what you're looking for, and a lot of patience

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Heh... if it's a problem where people shouldn't be doing the deletes, you can always add an INSTEAD OF DELETE trick that records the work station name, the session name, and ORIGINAL_LOGIN() along with Date and time of the attempt and then summarily reject the DELETE. It won't help if someone with high deity privs disables the trigger but even that would help narrow down the list of candidates the you might want to introduce to high velocity pork chops. 😀

--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)

Eric M Russell SSC Guru Points: 125562 More actions · Answer 10

This is a long shot, but assuming the user who executed the DELETE operation is still connection, and the last command they executed as the DELETE, then it should work. The following will query sys.dm_exec_connections to get all current connections, and then it will call DBCC INPUTBUFFER ( <SPID> ) to get the text of the last command executed by each connection.

FYI: For some reason either SQLServerCentral or my own firewall would block my posting of this T-SQL sample until I replaced the two DROP statements with "D R O P". You'll need to fix that before attempting to use this.

/*

Query the last command executed by each current connection.

It also includes details like connection time, host name, login name,

and IP address about each connection.

2016/11/16 Eric Russell

*/

IF object_id('tempdb..#connections') IS NOT NULL

D R O P TABLE #connections;

SELECT identity(INT, 1, 1) AS id

,cast(NULL AS VARCHAR(8000)) AS InputBuffer_EventInfo

,c.*

,qt.*

INTO #connections

FROM sys.dm_exec_connections AS c

OUTER APPLY sys.dm_exec_sql_text

(c.most_recent_sql_handle) AS qt;

IF object_id('tempdb..#inputbuffer') IS NOT NULL

D R O P TABLE #inputbuffer;

CREATE TABLE #inputbuffer (

EventType NVARCHAR(30) NULL

,Parameters SMALLINT

,EventInfo NVARCHAR(4000)

);

DECLARE @id INT = 0

,@maxid INT = (

SELECT max(id)

FROM #connections

)

,@spid INT

,@sql VARCHAR(8000);

WHILE (@id <= @maxid)

BEGIN

;

SELECT @spid = (

SELECT session_id

FROM #connections

WHERE id = @id

);

SELECT @sql = 'DBCC INPUTBUFFER ( '

+ cast(@spid AS VARCHAR(99)) + ' )';

DELETE

FROM #inputbuffer;

BEGIN TRY

INSERT INTO #inputbuffer (

EventType

,Parameters

,EventInfo

)

EXEC (@sql);

END TRY

BEGIN CATCH

PRINT 'Failed: ' + @sql;

END CATCH;

UPDATE #connections

SET InputBuffer_EventInfo = (

SELECT max(EventInfo)

FROM #inputbuffer

)

WHERE session_id = @spid;

SELECT @id = @id + 1;

END;

SELECT *

FROM #connections c

LEFT JOIN sys.dm_exec_sessions AS es

ON es.session_id = c.session_id

ORDER BY connect_time DESC;

GO

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho