December 3, 2015 at 7:45 am
This query works fine,
Can anyone add a join and make it useful so that it will show the name of the
person running the query( or stored proc )
Right now it is showing just an integer for user_id
USE master
GO
SELECT r.user_id, r.blocking_session_id , session_Id, DB_NAME(database_id) AS [Database], [text] AS [Query]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st
WHERE session_Id > 50 -- Consider spids for users only, no system spids.
AND session_Id NOT IN (@@SPID)
December 3, 2015 at 8:16 am
Can you not join to dm_exec_sessions here?
INNER JOIN sys.dm_exec_sessions AS des
ON des.session_id = r.session_id
December 3, 2015 at 8:16 am
mw112009 (12/3/2015)
This query works fine,Can anyone add a join and make it useful so that it will show the name of the
person running the query( or stored proc )
Right now it is showing just an integer for user_id
USE master
GO
SELECT r.user_id, r.blocking_session_id , session_Id, DB_NAME(database_id) AS [Database], [text] AS [Query]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st
WHERE session_Id > 50 -- Consider spids for users only, no system spids.
AND session_Id NOT IN (@@SPID)
You can add sys.dm_exec_sessions to get this information. https://msdn.microsoft.com/en-us/library/ms176013.aspx
Something like this.
SELECT s.login_name
, s.original_login_name
, r.user_id
, r.blocking_session_id
, r.session_Id
, DB_NAME(database_id) AS [Database]
, [text] AS [Query]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st
join sys.dm_exec_sessions s on s.session_id = r.session_id
WHERE r.session_Id > 50 -- Consider spids for users only, no system spids.
AND r.session_Id NOT IN (@@SPID)
--edit--
It seems Steve posted the same suggestion as I was typing. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply