How to Identify Session Used by SQL Server Management Studio? – Interview Question of the Week #151

Question: Which SQL Server session belongs to SQL Server Management Studio?

Answer: This one was not asked by an interviewer. At the end of an interview I helped conduct, a candidate asked us how to find the SSMS sessions. I liked the question because the answer is practical: first identify your own query window, then look at the program names of the sessions you are allowed to see.

In the query window you are using right now, @@SPID gives that window’s session ID. Run this first so you have a value you can recognize in the wider list.

SELECT @@SPID AS ThisQueryWindowSessionId;

The original answer used sp_who2 and pointed to its ProgramName column. The historical SSMS result below shows how to recognize the program name. The boxed entries are SSMS query sessions in that capture; your version and program names may differ.

EXEC sp_who2;

For a query you can sort and filter, use sys.dm_exec_sessions. The program_name value is the client program name recorded for each session; it is a helpful clue, not an authentication or security proof.

SELECT session_id,
       login_name,
       host_name,
       program_name,
       status,
       login_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
  AND program_name LIKE N'Microsoft SQL Server Management Studio%'
ORDER BY session_id;

If you have only permission to see your own session, the wider list will be limited. I still start with @@SPID when answering this in an interview: it connects the window in front of me to the session ID in the result.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.