Sqlserver限制账户在哪些ip下才可以访问数据库

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：包含建立连接的客户端的主机名。 如果客户端和服务器名称相同，则此值为“”。 否则，此值为客户端的 IP 地址。

备注：限制用户testuser只能通过ip 172.22.136.240、172.22.137.251或本机来登陆

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','')
     ROLLBACK;
END;
1
2
3
4
5
6
7
8
9
10
11
12

删除触发器

Drop TRIGGER connection_limit_trigger 
Drop TRIGGER connection_limit_trigger ON DATABASE 
1
2

报错如下
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  
1

查询触发器名称和触发器类型

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
1

通过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','')
     ROLLBACK;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13