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?
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.
I think you're asking the wrong question here.
Why on earth is people you're not trusting given enough privileges in your database to actually delete that sproc in the first place? That's the question you need to ask.
It's like trying to find out who robbed your house after you left the key out on the porch.
Assuming an "average" default installation of SQL Server, sitting down at your server right now, you will not be able to determine this information. By default, SQL does not log or track this kind of activity.
(There are a number of ways you can log this information (DDL triggers), but that doesn't help you now--it would only help with future activity.)
Chris mentioned going reviewing the transaction log and extracting what information is present there. This would work, but SQL 2005 does not provide any "native" functionality for sifting through transaction logs. You would need a third-party tool to do so. And that only applies for as long as that data is in the transaction log; if the database recovery mode is set to "simple", that data will be wiped from the log--sooner rather than later. (If your database is actively used, it may already be gone.)
Remus Rusanu outlined how to query the system trace. Very cool, I'm upvoting that one! As he said, this too has a limited shelf life--you should probably make copies of those files now before they get overwritten.
If the above tactics are not possible, restoring and reviewing backups might track down when it occured. This again depends on your recovery mode and whatever backup files you have. If you can do point-in-time recoveries on transaction log backups, you should be able to get a pretty close estimate on when it was dropped; If you only have complete or differential backups, you'll get less precision (e.g. was there in the 1:00pm backup, was not in the 2:00pm backup, must have been dropped between 1 and 2.)
As for who dropped it (or rather, via which SQL login was it dropped), unless you've got some intentionally configured process installed and running, I do not believe you can extract that information. A starting point would be to determine who (rather, which logins) could perform the drop, and go from there. Is your SQL installation configured to log succesful logins in the Windows Event logs? Is the domain set to track domain logins? ...though neither will help if SQL authentication was involved.
It might not be possible, but you might be able to work up some reasonable guesses. Good luck!
You should be able to go back through a transaction log and be able to at least find out when that procedure was dropped from the database. As to who the users was, you might be able to see who was logged in to the system at that time, and be able to narrow it down somewhat.
Hope this helps some.
You can use below script to identify suspect user:
SELECT
te.name AS eventtype
,t.loginname
,t.spid
,t.starttime
,t.objectname
,t.databasename
,t.hostname
,t.ntusername
,t.ntdomainname
,t.clientprocessid
,t.applicationname
FROM sys.fn_trace_gettable
(
CONVERT
(VARCHAR(150)
,(
SELECT TOP 1
value
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2
)),DEFAULT
) T
INNER JOIN sys.trace_events as te
ON t.eventclass = te.trace_event_id
WHERE eventclass=164
