November 16, 2016 at 6:28 am
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
November 16, 2016 at 6:40 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2016 at 6:45 am
Thanks Sir 🙂
November 16, 2016 at 6:46 am
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?
November 16, 2016 at 7:03 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2016 at 7:08 am
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
November 16, 2016 at 7:17 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 16, 2016 at 7:28 am
Thanks Sir for the update on this 🙂
November 16, 2016 at 7:40 am
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
November 16, 2016 at 7:47 am
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
Change is inevitable... Change for the better is not.
November 16, 2016 at 8:14 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply