SQLServer中使用登录触发器限制用户的访问地址

0、使用sa账号登录

1、创建test登录账号
CREATE LOGIN test WITH PASSWORD = 'Abcd@1234'
GO

2、创建登录触发器
drop TRIGGER [tr_limit_ip] ON ALL SERVER ;

CREATE TRIGGER [tr_limit_ip]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN


IF ORIGINAL_LOGIN()= 'test'
AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.31.181','192.168.31.17')
     ROLLBACK;
END;

SELECT 
a.[session_id],a.[login_time],a.[host_name],
a.[original_login_name],b.[client_net_address]
FROM MASTER.sys.dm_exec_sessions a 
INNER JOIN MASTER.sys.dm_exec_connections b 
ON a.session_id=b.session_id;