Context: SQL Server RDBMS
Often times in database environments where more than one person possesses sysadmin role things could get messy. Especially when you realize an object (Database,SP, Table etc) was deleted and you have no idea by whom and when this object was deleted. Unfortunately SQL server error log
doesn’t do a very good job of showing logs related to deleted objects. There are answers; you just need to know where to look. SQL server default trace comes to your rescue. The TSQL below helps you identify who deleted the object and when. This can be used in the both events of knowing or not knowing the database/object name.
SELECT DatabaseID,NTUserName,HostName,LoginName,StartTime FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE TE.trace_event_id =47 AND T.DatabaseName = 'delete' -- 47 Represents event for deleting objects.
The result will looks like this:
Thanks for reading.