在不使用觸發器的情況下查找在 SQL Server 中觸發查詢的客戶端的身份？
我目前正在使用更改數據擷取 (CDC)來跟踪數據更改，並且我希望跟踪送出進行更改的查詢的客戶端的主機名和 IP 地址。如果有 5 個不同的客戶端通過相同的使用者名登錄，那麼一個人面臨的難題是跟踪 5 個中的哪一個觸發了查詢。我發現的其他似是而非的解決方案包括使用以下命令更改 CDC 表：
ALTER TABLE cdc.schema_table_CT ADD HostName nvarchar(50) NULL DEFAULT(HOST_NAME())但是，這將返回觸發查詢的伺服器的主機名，而不是觸發查詢的客戶端的主機名。
有沒有辦法解決這個問題？有助於記錄客戶端的主機名或 IP 地址（或某種其他類型的唯一身份）的東西。我不想使用觸發器，因為它會減慢系統速度，而且 CDC 還會生成系統表，因此顯然不可能使用觸發器。
我不確定 CDC，但如果登錄有
view server state permission，您可以使用 DMV 獲取一些資訊。這在此處的聯機叢書中給出。我更改了查詢以添加列，這些列將為您提供
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
當您說“不使用觸發器”時，您是指任何觸發器還是表上的逐行觸發器？
我問是因為您可以通過明智地使用該功能來獲得您想要的東西
CONTEXT_INFO()，但您需要確保SET CONTEXT_INFO在您的操作發生之前正確呼叫它。這樣做的一個地方可能是伺服器級登錄觸發器（即不是數據庫/對象級觸發器），如下所示：
USE master GO CREATE TRIGGER tr_audit_login ON ALL SERVER WITH EXECUTE AS 'sa' AFTER LOGON AS BEGIN BEGIN TRY DECLARE @eventdata XML = EVENTDATA(); IF @eventdata IS NOT NULL BEGIN DECLARE @spid INT; DECLARE @client_host VARCHAR(64); SET @client_host = @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'VARCHAR(64)'); SET @spid = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'INT'); -- pack the required data into the context data binary -- (spid is just an example of packing multiple data items in a single field: you would probably use @@SPID at the point of use, instead) DECLARE @context_data VARBINARY(128); SET @context_data = CONVERT(VARBINARY(4), @spid) + CONVERT(VARBINARY(64), @client_host); -- persist the spid and host into session-level memory SET CONTEXT_INFO @context_data; END END TRY BEGIN CATCH /* do better error handling here... * logon trigger can lock all users out of server, so i am just swallowing everything */ DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE(); RAISERROR('%s', 10, 1, @msg) WITH LOG; END CATCH END然後，您可以將預設約束添加到您的表中，以儲存上下文（用於插入速度）：
ALTER TABLE cdc.schema_table_CT ADD ContextInfo varbinary(128) NULL DEFAULT(CONTEXT_INFO())一旦你有了它，你可以
ContextInfo用一些切片和骰子來查詢該列：SELECT * ,spid = CONVERT(INT, SUBSTRING(ContextInfo, 1, 4)) ,client = CONVERT(VARCHAR(64), SUBSTRING(ContextInfo, 5, 64)) FROM cdc.schema_table_CT從技術上講，您可以這樣做
SUBSTRING並將CONVERT其作為預設約束的一部分，並將客戶端 IP 儲存在那裡，但將整個上下文儲存在那裡可能會更快（就像在 every 上所做的那樣INSERT），並且只提取 a 中的值SELECT當你需要它們時。我可能傾向於將所有我的
SUBSTRING和CONVERT呼叫包裝在一個單行內聯表值函式中，我會CROSS APPLY在必要時這樣做。這將拆包邏輯保持在一個地方：CREATE FUNCTION fn_context ( @context_info VARBINARY(128) ) RETURNS TABLE AS RETURN ( SELECT spid = CONVERT(INT, SUBSTRING(@context_info, 1, 4)) ,client = CONVERT(VARCHAR(64), SUBSTRING(@context_info, 5, 64)) ) GO SELECT * FROM cdc.schema_table_CT s CROSS APPLY dbo.fn_context(s.ContextInfo) c請注意，這
CONTEXT_INFO只是一個 128 字節的VARBINARY. 如果您需要的數據多於 128 字節，我將創建一個表來保存所有數據，將該“會話”作為行插入到登錄觸發器中的表中，並設置CONTEXT_INFO為該表的代理鍵值您還應該注意，由於它只是一個預設約束，因此具有適當特權的使用者覆蓋靜態表中的上下文數據是微不足道的。當然，對於“審計”樣式表中的所有其他列也是如此。
如果它可以是一個持久的計算列，而不是預設值，那就太好了，但是該
CONTEXT_INFO()函式是不確定的，所以它是不行的（你可能可以FUNCTION在 a 周圍使用一些技巧VIEW，但我不會）。
SET CONTEXT_INFO對於具有足夠訪問權限的使用者來說，呼叫自己並搞砸您的一天（例如使用虛假值或特製的儲存注入）也是微不足道的，因此請謹慎對待內容，在顯示之前對其進行編碼，並處理異常好吧。至於主機名，我認為
ClientHost元素EVENTDATA()為您提供了 IP 地址（或<local machine>指示符）。雖然從技術上講，您可以使用 CLR 對主機名進行反向 DNS 查找，但對於 each 執行這些操作往往太慢INSERT，因此我建議不要這樣做。如果您必須擁有主機名，您可能希望使用 SQL 代理作業定期使用本地 DHCP 伺服器或 DNS 區域文件中的目前租約填充單獨的表，作為帶外程序，並
LEFT JOIN在未來的查詢（或包裝在一個標量FUNCTION中，為預設約束提供一個值，用於時間點）。同樣，您應該注意，如果應用程序有任何類型的面向公眾的組件，IP 地址和主機名是不可靠的（例如，由於 NAT）。即使它不是面向公眾的，大多數 IP/主機名映射都有一個基於時間的組件，您可能需要考慮這一點。
最後，在實現登錄觸發器之前，打開伺服器的專用管理員連接可能是值得的。如果登錄觸發器以任何方式中斷，它可以阻止所有使用者登錄（包括系統管理員帳戶）：
USE master GO -- you may want to do this, so you have a back-out if the login trigger breaks login EXEC sp_configure 'remote admin connections', 1 GO RECONFIGURE GO如果您確實被鎖定，DAC 可用於刪除或禁用登錄觸發器：
C:\> sqlcmd -S localhost -d master -A 1> DISABLE TRIGGER tr_audit_login ON ALL SERVER 2> GO