Thursday, October 1, 2015

Easy way to find who dropped table

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

drop table test1234
go
 SELECT
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
--get [Transaction Id] and you can nopw find the loginname
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...