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