Is it possible to find out who called a stored procedure in SQL Server 2005

Question

Is it possible to find out who called a stored procedure? I'm using the following query to identify the execution count etc. but I'm unable to identify which job / trigger / process is calling it. Any ideas about it please?

SELECT  
    a.execution_count, OBJECT_NAME(objectid) Name,
    (CASE WHEN a.statement_end_offset = -1 
             THEN LEN(CONVERT(nvarchar(max), b.text)) * 2
             ELSE a.statement_end_offset
     END - a.statement_start_offset) / 2),
    b.dbid, dbname = db_name(b.dbid), b.objectid,
    a.creation_time, a.last_execution_time, a.* 
FROM  
    sys.dm_exec_query_stats a 
CROSS APPLY 
    sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE 
    OBJECT_NAME(objectid) = 'Rebuild_Indexes' 
ORDER BY 
    a.last_execution_time
ESCquery_text = SUBSTRING(b.text,a.statement_start_offset/2,

Answer 1

Use Adam Machanic's Who is Active stored procedure - this returns all sorts of info about active statements, including the user who launched them.

Answer 2

If you want to see who is executing a stored procedure, one way to go about this is to create a server-side Trace and capture the SP:Completed event. The data provided by this event class will give you all the caller information you should need.

Reference: BOL documentation on the SP:Completed Event Class

Answer 3

Well if you fire up SQL profiler, it will show you the network id of the NT user, I suggest you run trace profiler on the server its self if this is an occasional usage. alternately if you are not using simple recovery mode, then the answer will be in the transaction log backup. However this is not easy to analyse or read.