Schema Change History

Question

Post reply

Schema Change History

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 30, 2015 at 9:59 pm

#309540

How can I easily identify who dropped a table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Like this?

http://www.sqlskills.com/blogs/paul/finding-out-who-dropped-a-table-using-the-transaction-log/

or

http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Thanks.

I saw the first article but I did not see where you identify the Login,

How do you read the transaction log?

The second Example may be easier.

Thanks again. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I do not see where this code is reading a Transaction Log?

SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]

FROM fn_dblog (NULL, NULL)

WHERE [Transaction Name] = N’DROPOBJ';

GO

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Check out Paul Randal's article...

Finding out who dropped a table using the transaction log[/url]

Looks like this returns who ran the offending code:

SUSER_SNAME ([Transaction SID]) AS [User]

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

Welsh Corgi (6/30/2015)
I do not see where this code is reading a Transaction Log?
SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ';
GO

The fn_dblog function is what reads the log.

Cheers!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Jacob Wilkins (7/1/2015)
Welsh Corgi (6/30/2015)
I do not see where this code is reading a Transaction Log?
SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ';
GO
The fn_dblog function is what reads the log.
Cheers!

Thanks a lot. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Almighty SSCrazy Points: 2001 More actions · Answer 7

http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/

check this out

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

If the default trace is enabled (which is should be), then that can be used to query DDL events, including more information about the session and user than what the transaction log offers.

SELECT DISTINCT

gt.StartTime

,e.[Name] EventName

,gt.[ObjectName]

,gt.SPID

,gt.[HostName]

,gt.[LoginName]

,gt.[ApplicationName]

,ec.client_net_address

FROM fn_trace_gettable(

(

SELECT cast(value as varchar(8000))

FROM ::fn_trace_getinfo(0)

where traceid = 1 and property = 2), DEFAULT

) gt

JOIN sys.trace_events e ON gt.eventclass = e.trace_event_id

LEFT JOIN sys.dm_exec_connections EC On EC.session_id = gt.SPID

WHERE gt.starttime > dateadd(hour,-48,GETDATE())

AND e.Name like 'Object:Deleted'

ORDER BY gt.starttime desc;

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