Trigger for logon
官方文件
https://docs.microsoft.com/zh-cn/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver16
https://docs.microsoft.com/zh-cn/sql/relational-databases/triggers/capture-logon-trigger-event-data?view=sql-server-ver16

使用 EVENTDATA 函式來返回LOGON事件中ClientHost來判斷：
<clienthost>
包含建立連線的客戶端的主機名。 如果客戶端和伺服器名稱相同，則此值為“<local_machine>”。 否則，此值為客戶端的 IP 地址。

USE master;  
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'testuser'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'testuser'
AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('172.22.136.240','172.22.137.251','<local machine="">')
     ROLLBACK;
END;
備註：限制使用者testuser只能透過ip 172.22.136.240、172.22.137.251或本機來登陸

刪除觸發器
Drop TRIGGER connection_limit_trigger 
Drop TRIGGER connection_limit_trigger ON DATABASE 
報錯如下
Cannot drop the trigger 'connection_limit_trigger', because it does not exist or you do not have permission.

Drop TRIGGER connection_limit_trigger  ON ALL SERVER--正常執行

禁用觸發器
Disable TRIGGER connection_limit_trigger  ON ALL SERVER

查詢觸發器名稱和觸發器型別
select a.name trigger_name, b.type_desc trigger_type,a.*,b.* from sys.server_triggers a inner join sys.server_trigger_events b on a.object_id=b.object_id

透過master.sys.dm_exec_connections欄位client_net_address來判斷
程式碼邏輯：使用者testuser只能透過本地和ip 172.22.136.240、172.22.137.251登陸
結果：發現這個觸發器建立後，所用使用者都登陸不了
原因：會話只有登陸上了才會在master.sys.dm_exec_connections欄位client_net_address上有記錄，因為我們使用了觸發器來驗證登陸，都沒有登陸上就不會有記錄，所以這個觸發會讓所有使用者都無法登陸
CREATE  TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'testuser'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'testuser' AND
(select top 1 b.client_net_address from sys.dm_exec_sessions a inner join master.sys.dm_exec_connections b on
a.session_id=b.session_id and a.login_name='testuser'
order by login_time desc
)
not in('172.22.136.240','172.22.137.251','<local machine="">')
     ROLLBACK;
END;</local></local></local_machine></clienthost>

Sqlserver限制賬戶在哪些ip下才可以訪問資料庫

相關文章