none
How to find the logins,program name and databases for a session id?

    Question

  • I used the following the following query.
    select pr.spid ,pr.dbid,pr.program_name,pr.loginame,ss.session_id,ss.host_name,ss.login_time,db.name from master.dbo.sysprocesses pr,sys.dm_exec_sessions ss,sys.databases db
    where pr.spid = ss.session_id and pr.dbid = db.database_id
    order by ss.login_time desc

    Is spid in master.dbo.sysprocesses the same as session_id in sys.dm_exec_sessions and dbid in master.dbo.sysprocesses the same as database_id in sys.databases?
    My intention is to get the active sessions with the program name,login Name and database name corresponding to the session?

    Thursday, January 09, 2014 7:59 AM

Answers

  • Try the below:

    SELECT
            [des].session_id
    ,       [des].[status]
    ,       [des].login_name
    ,       DB_NAME(der.database_id) AS database_name
    ,       [des].[program_name]
    FROM
            sys.dm_exec_sessions [des]
    LEFT JOIN
            sys.dm_exec_requests der
    ON
            [des].session_id = der.session_id
    where des.session_id >50
    ORDER BY
            [des].session_id;

    Thursday, January 09, 2014 8:03 AM
  • Thank you Latheesh. Could you clarify the following-:

    1)Why you included the clause session_id > 50?

    < 50 is all system process which I dont think you are interested.

    2)Is there any problem with the query I used?

    Your query is also fine but using DMV is good idea and sys.sysprocess is old system view.also your query will return data for system process so query was modified AFAIT

    Could any body please clarify?



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 09, 2014 8:30 AM
  • 1)Why you included the clause session_id > 50?

    Sessions with session_id <= 50 are system processes.

    2)Is there any problem with the query I used?

    You query was better than Latheesh's. To wit, his query will only show the database for sessions who are actually running something.

    On SQL 2005 and SQL 2008, this is the best solution:

    SELECT  des.session_id, des.[status], des.login_name,
            d.name AS database_name, des.program_name
    FROM    sys.dm_exec_sessions des
    JOIN    sys.sysprocesses p ON des.session_id = p.spid
    LEFT JOIN sys.databases d ON p.dbid = d.database_id
    WHERE   des.session_id > 50
      AND   p.ecid =0
    ORDER BY des.session_id;

    The condition p.ecid = 0 is needed to weed out extra rows when there are parallism in force.

    On SQL 2012, you don't need sysprocesses, but this works:

    SELECT  des.session_id, des.[status], des.login_name,
            d.name AS database_name, des.program_name
    FROM    sys.dm_exec_sessions des
    LEFT JOIN sys.databases d ON des.database_id = d.database_id
    WHERE   des.session_id > 50
    ORDER BY des.session_id;


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 09, 2014 10:09 PM

All replies

  • Try the below:

    SELECT
            [des].session_id
    ,       [des].[status]
    ,       [des].login_name
    ,       DB_NAME(der.database_id) AS database_name
    ,       [des].[program_name]
    FROM
            sys.dm_exec_sessions [des]
    LEFT JOIN
            sys.dm_exec_requests der
    ON
            [des].session_id = der.session_id
    where des.session_id >50
    ORDER BY
            [des].session_id;

    Thursday, January 09, 2014 8:03 AM
  • Thank you Latheesh. Could you clarify the following-:

    1)Why you included the clause session_id > 50?

    2)Is there any problem with the query I used?

    Could any body please clarify?

    Thursday, January 09, 2014 8:27 AM
  • Thank you Latheesh. Could you clarify the following-:

    1)Why you included the clause session_id > 50?

    < 50 is all system process which I dont think you are interested.

    2)Is there any problem with the query I used?

    Your query is also fine but using DMV is good idea and sys.sysprocess is old system view.also your query will return data for system process so query was modified AFAIT

    Could any body please clarify?



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, January 09, 2014 8:30 AM
  • Thank you Shanky.
    Thursday, January 09, 2014 10:07 AM
  • 1)Why you included the clause session_id > 50?

    Sessions with session_id <= 50 are system processes.

    2)Is there any problem with the query I used?

    You query was better than Latheesh's. To wit, his query will only show the database for sessions who are actually running something.

    On SQL 2005 and SQL 2008, this is the best solution:

    SELECT  des.session_id, des.[status], des.login_name,
            d.name AS database_name, des.program_name
    FROM    sys.dm_exec_sessions des
    JOIN    sys.sysprocesses p ON des.session_id = p.spid
    LEFT JOIN sys.databases d ON p.dbid = d.database_id
    WHERE   des.session_id > 50
      AND   p.ecid =0
    ORDER BY des.session_id;

    The condition p.ecid = 0 is needed to weed out extra rows when there are parallism in force.

    On SQL 2012, you don't need sysprocesses, but this works:

    SELECT  des.session_id, des.[status], des.login_name,
            d.name AS database_name, des.program_name
    FROM    sys.dm_exec_sessions des
    LEFT JOIN sys.databases d ON des.database_id = d.database_id
    WHERE   des.session_id > 50
    ORDER BY des.session_id;


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 09, 2014 10:09 PM
  • Thank you Erland.
    Tuesday, January 14, 2014 9:15 AM
  • Just a hint that system sessions *can* bleed over 50. In sys.dm_exec_session, you have a column named is_system_process (or something similar) which is a more reliable way to filter to only user connectons.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, January 20, 2014 1:30 PM