SQL Server trigger - connection info

Question

is it possible to get MSSQL connection info?
not onli SUSER_ID(), SUSER_NAME(), ORIGINAL_LOGIN(), BUT other like:

• IP
• Connection string
• ect..

Answer 1

You can get some more information from sys.dm_exec_connections:

e.g.

SELECT * 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID

This will get the connection info available for the current process (SPID). This doesn't give the full connection string, but does give some more info like IP address (client_net_address).

This will work for SQL Server 2005 and above.

Answer 2

You didn't mention the version of SQL Server that you're using, but this should work for SQL 2005 and above. You can change the @@SPID as needed.

SELECT
    conn.session_ID as SPID,
    conn.client_net_address as IPAddress,
    sess.host_name as MachineName,
    sess.program_name as ApplicationName,
    login_name as LoginName
FROM
    sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sess ON
    conn.session_ID = sess.session_ID
WHERE
    conn.session_ID = @@SPID