alter TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'luckysa'
FOR LOGON
AS
BEGIN
--使用testuser这个帐号的连接
IF ORIGINAL_LOGIN()= 'luckysa'
AND
--以下是判断非本地的,你可以不使用
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE [host_name]<>'localhost' AND
original_login_name = 'luckysa') > 0
--以下代码中的IP是被允许的
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='luckysa'
order by login_time desc
)
not in('192.168.1.153')
ROLLBACK;
END;
第二种
CREATE TRIGGER [tr_connection_limit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
--限制test这个帐号的连接
IF ORIGINAL_LOGIN()= 'test'
--允许test在本机和下面的IP登录
AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.1.50','192.168.1.120')
ROLLBACK;
ON ALL SERVER WITH EXECUTE AS 'luckysa'
FOR LOGON
AS
BEGIN
--使用testuser这个帐号的连接
IF ORIGINAL_LOGIN()= 'luckysa'
AND
--以下是判断非本地的,你可以不使用
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE [host_name]<>'localhost' AND
original_login_name = 'luckysa') > 0
--以下代码中的IP是被允许的
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='luckysa'
order by login_time desc
)
not in('192.168.1.153')
ROLLBACK;
END;
第二种
CREATE TRIGGER [tr_connection_limit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
--限制test这个帐号的连接
IF ORIGINAL_LOGIN()= 'test'
--允许test在本机和下面的IP登录
AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.1.50','192.168.1.120')
ROLLBACK;
END;
注:DROP TRIGGER connection_limit_trigger ON ALL SERVER 删除触发器