Find who dropped, created or altered the table, SP or any object

Find who dropped, created or altered the table, SP or any object

If any database object is dropped, created or altered accidentally, sometimes you may need information about:

Who dropped, created or altered the object? When culprit not accept the mistake.

When the object was dropped, created or altered? Especially drop time of table is required for point in time recovery from database backups.

These changes are not recorded in SQL Server Error Log but are recorded in default trace. If you have not disabled the default trace and started looking into the issue soon after change occurred you could get this information. The path of trace file is “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\”. The folder MSSQL10.SQLEXPRESS may be different according to your SQL Server instance name. Here you would find 5 profiler trace files named as “log_1”, “Log_2”, etc. The file number is increased by time (in following example I used “log_16.trc”). Find the file that is most recently modified and use the below t-sql query to open that file:

SELECT ObjectName, DatabaseName, StartTime, NTDomainName, HostName, NTUserName, LoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\log_16.trc', default)

WHERE objectname is not null  

You can start a trace as default in two ways:

1. create a stored procedure that starts a trace# and then set that SP as startup as following:

CREATE PROC sp_StartTrace
AS
DBCC TRACEON (1204) 
GO

sp_procoption sp_StartTrace , 'STARTUP', 'ON'

2. Start SQL Server at Run window using NET START command with -T switch as below:
net start SQLSERVER -T 1204