Sqlserver restricts the ip under which accounts can access the database

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：Contains the hostname of the client making the connection. If the client and server names are the same,则此值为“<local_machine>”. 否则,This value is client-side IP 地址.

备注：限制用户testuser只能通过ip 172.22.136.240、172.22.137.251or this machine to log in

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;

删除触发器

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  

Query trigger name and trigger type

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来判断
代码逻辑：用户testuserOnly by local andip 172.22.136.240、172.22.137.251登陆
结果：After discovering that this trigger is created,All users cannot log in
原因：The session will only exist if you are logged inmaster.sys.dm_exec_connections字段client_net_address上有记录,Because we used a trigger to authenticate the login,If you don't log in, there will be no record,So this trigger will prevent all users from logging in

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;