Skip to main content
deleted 41 characters in body
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

I am not sure about CDC, but if the login has view server state permission you can use DMVDMVs to get fewsome information. I cannot post this as comment so posting this as answer. 

This is given in BOLBooks Online here. I changed the query to add columns which would give you the IP address:

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, 
    c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    s.client_interface_name,c.local_net_address, c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, 
    s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track

I am not sure about CDC but if login has view server state permission you can use DMV to get few information. I cannot post this as comment so posting this as answer. This is given in BOL here I changed the query to add columns which would give you IP address

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, 
    c.auth_scheme, s.host_name, s.program_name, 
    s.client_interface_name,c.local_net_address,c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, 
    s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;--session ID you want to track

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information. 

This is given in Books Online here. I changed the query to add columns which would give you the IP address:

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme,
    s.host_name, s.program_name, s.client_interface_name,
    c.local_net_address, c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;  --session ID you want to track
Source Link
Shanky
  • 19.1k
  • 4
  • 36
  • 58

I am not sure about CDC but if login has view server state permission you can use DMV to get few information. I cannot post this as comment so posting this as answer. This is given in BOL here I changed the query to add columns which would give you IP address

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, 
    c.auth_scheme, s.host_name, s.program_name, 
    s.client_interface_name,c.local_net_address,c.client_net_address, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, 
    s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = SPID;--session ID you want to track