依據 SPID 取得用戶端 IP 位址：SQL Server 系統預存程序實作

預存程序：dbo.SP_SPIDtoIP

CREATE PROCEDURE dbo.SP_SPIDtoIP @SPID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 從 sysprocesses 取得 MAC 位址 (NET_ADDRESS)
    DECLARE @Mac VARCHAR(12);
    SELECT @Mac = NET_ADDRESS FROM master..sysprocesses WHERE SPID = @SPID;
    IF @Mac IS NULL OR @Mac = ''
    BEGIN
        RAISERROR('找不到指定 SPID 的 MAC 位址', 16, 1);
        RETURN;
    END

    -- 2. 格式化 MAC 為 xx-xx-xx-xx-xx-xx 以便比對
    DECLARE @MacDisplay VARCHAR(18);
    SELECT @MacDisplay = LEFT(@Mac,2)+'-'+SUBSTRING(@Mac,3,2)+'-'+SUBSTRING(@Mac,5,2)+'-'
                        +SUBSTRING(@Mac,7,2)+'-'+SUBSTRING(@Mac,9,2)+'-'+SUBSTRING(@Mac,11,2);

    -- 3. 執行 arp -a 解析 IP-MAC 對應表
    CREATE TABLE #temp (OUTPUT VARCHAR(255) NULL);
    INSERT INTO #temp EXEC master..xp_cmdshell 'arp -a';
    IF @@ERROR <> 0
    BEGIN
        RAISERROR('xp_cmdshell 執行 arp -a 失敗', 16, 1);
        DROP TABLE #temp;
        RETURN;
    END

    -- 4. 擷取對應 IP
    DECLARE @IP VARCHAR(15);
    SELECT @IP = SUBSTRING(OUTPUT, 3, 15)
    FROM #temp
    WHERE OUTPUT LIKE '%' + @MacDisplay + '%';

    DROP TABLE #temp;
    SET NOCOUNT OFF;

    SELECT @IP AS ClientIP;
END
GO

運作原理

1. master..sysprocesses 的 NET_ADDRESS 欄位記錄用戶端網卡 MAC 位址（無連線時為空）。
2. arp -a 顯示本機 ARP 快取表，含 IP-MAC 對應。
3. 將 MAC 格式化為標準 `xx-xx-xx-xx-xx-xx`，在 ARP 輸出中模糊比對，擷取前 15 字元作為 IP。

前置條件與限制

• 需啟用 xp_cmdshell（sp_configure 'xp_cmdshell', 1; RECONFIGURE）。
• 呼叫者需具備執行 xp_cmdshell 權限（預設僅 sysadmin）。
• ARP 快取具時效性（預設 2-10 分鐘），連線閒置過久可能已失效。
• 多網卡/虛擬網卡環境下 MAC 可能非實體客戶端網卡。
• SQL Server 2008+ 建議改用 CONNECTIONPROPERTY('client_net_address') 直接取得 IP，免用 xp_cmdshell。

呼叫範例

EXEC dbo.SP_SPIDtoIP 52;  -- 傳入目標 SPID