Sql-server – How to find when the stored procedure was deleted and who deleted it

sql serversql-server-2005

Actually, one of the critical jobs failed while executing.

In the error message it was found that the failure is because it was missing a stored procedure.

Now how do I find out when was the stored procedure was affected by the user. How do I find out which user did it and when he did it?

Best Answer

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.

Related Topic