How to find when the stored procedure was deleted and who deleted it?

You get the administrative trace:

select * from fn_trace_getinfo(NULL)
where property=2
and traceid = 1

The you look into the administrative trace for events of class 47 Object:Deleted Event Class on object types 8727 Stored Procedure:

select * from fn_trace_gettable('....trc', -1)
where EventClass = 47
and ObjectType=8727

The administrative trace is periodically recycled and about 4-5 traces are kept, you should use the name of the oldest trc file still present.

If the procedure is critical then the DBA should had make sure only authorized personel can modify it or drop it. And it should had in place auditing of schema changes. This is not the fault of whoever dropped the procedure, but entirely the DBA fault.

There is no way to find out this info by default because SQL Server doesn’t support this. If your database is in full recovery mode you can try to read transaction log and see when DROP PROCEDURE statement was executed. Unfortunately there is no easy way to do this.

You can try using some of the third party tools such as ApexSQL Log or Quest Toad but even with these tools I’m not sure you’ll be able to find out the username of who did this.

Another option you can try is to check out fn_dblog function and see if you can make any use of that. Problem here is that this function is not well documented.