Sometime I have been asked that how to find out who
dropped/altered/created table/stored procedure. Well, SQL 2005 has
out-of-the-box solution to this problem.
Schema Change History
is the report which would give details. This report takes data from
default trace which is enabled by default in SQL Server 2005 default
installation. Below command will help you in finding current status.
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'default trace';