是否有可能找出谁在sql server 2005中调用了存储过程

Is it possible to find out who called a store 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,

4 个解决方案

#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.

使用Adam Machanic的Who is Active存储过程 - 这将返回有关活动语句的各种信息，包括启动它们的用户。

#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.

如果要查看谁正在执行存储过程，那么解决此问题的一种方法是创建服务器端Trace并捕获SP：Completed事件。此事件类提供的数据将为您提供所需的所有呼叫者信息。

Reference: BOL documentation on the SP:Completed Event Class

参考：SP上的BOL文档：已完成的事件类

#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.

好吧，如果您启动SQL分析器，它将显示NT用户的网络ID，我建议您在服务器上运行跟踪分析器，如果这是偶尔使用。或者，如果您不使用简单恢复模式，则答案将在事务日志备份中。然而，这不容易分析或阅读。

#4

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

使用动态管理视图选项。 DMV提供了一个简单而熟悉的关系界面，用于从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'

#1