SQL 禁用某个账户在某个IP进行登录

发布日期:2021-09-18

SELECT *FROM sys.dm_exec_connections

WHERE session_id = @@SPID

if exists( select name from sysobjects where name ='tr_connection_limit')
DROP TRIGGER  tr_connection_limit ON ALL SERVER 
go

CREATE TRIGGER [tr_connection_limit]
ON ALL SERVER WITH EXECUTE AS 'ForDev'
FOR LOGON
AS
BEGIN

--限制test这个帐号的连接
IF ORIGINAL_LOGIN()= 'ForDev'
--允许test在本机和下面的IP登录
AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
 IN('<local machine>','192.168.99.4','192.168.4.2')
     ROLLBACK;
END;