Thursday, October 1, 2015

Easy way to find who deleted rows in a table

create table  test1234( id int)
go
insert into test1234 Values(1)
go
delete from test1234

--find deleted rows
SELECT
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
   
FROM
    fn_dblog(NULL, NULL)
WHERE
    Operation = 'LOP_DELETE_ROWS'
--We found the transaction ID from the above command, now get the transaction SID of the user who has deleted the data check table name also
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:0000076b'
AND
    [Operation] = 'LOP_BEGIN_XACT'


---get login name from  [Transaction SID]
SELECT SUSER_SNAME(0xCCE723D8CEDF0244B5963964EC5180ED)

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...