CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user362283
user362283

Reputation: 95

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

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,

Upvotes: 5

Views: 1756

Answers (4)

dantefs
dantefs

Reputation: 174

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

Upvotes: 3

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use option with Dynamic Management Views. DMVs provide a simple and familiar relational interface for gathering critical system information from your SQL Server.

SELECT DB_NAME(der.database_id) AS databaseName,
       OBJECT_NAME(objectid),
       der.session_id,
       login_name,
       USER_NAME(der.user_id) AS user_name,
       der.command,
       dest.text AS [CommandText],
       des.login_time,
       des.[host_name],
       dec.client_net_address,
       des.[program_name],       
       der.status
FROM sys.dm_exec_requests der
  INNER JOIN sys.dm_exec_connections dec ON der.session_id = dec.session_id
  INNER JOIN sys.dm_exec_sessions des ON der.session_id = des.session_id
  CROSS APPLY sys.dm_exec_sql_text (sql_handle) AS dest
WHERE des.is_user_process = 1 
  --AND OBJECT_NAME(objectid) = 'Rebuild_Indexes'

Upvotes: 1

Ian P
Ian P

Reputation: 1724

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.

Upvotes: 2

Thomas Stringer
Thomas Stringer

Reputation: 5862

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

Upvotes: 3

Related Questions