SQLServer如何获取客户端IP

SQLServer如何获取客户端IP

很多用户询问如何通过SQLServer获取客户端IP从而定位一些问题，比如链接泄露，其实主要是利用几个相关视图，如下给出一些SQL方便用户排查

当前链接

    SELECT  CONNECTIONPROPERTY('PROTOCOL_TYPE') AS PROTOCOL_TYPE,
            CONNECTIONPROPERTY('CLIENT_NET_ADDRESS') AS CLIENT_NET_ADDRESS        
            

所有链接

    SELECT
      SP.SPID,
      SP.LOGINAME,
      SP.LOGIN_TIME,
      SP.HOSTNAME,
      SP.PROGRAM_NAME,
      DC.CLIENT_TCP_PORT,
      DC.CLIENT_NET_ADDRESS
    FROM SYS.SYSPROCESSES AS SP
    INNER JOIN SYS.DM_EXEC_CONNECTIONS AS DC
      ON SP.SPID = DC.SESSION_ID
    WHERE SP.SPID > 50
    AND DC.AUTH_SCHEME='SQL'
    

查看更详细的链接参数配置

    SELECT * FROM SYS.DM_EXEC_SESSIONS WHERE SESSION_ID=之前获取的SPID