34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS 'sa_Public'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = 'sa_Public'
AND ( SELECT TOP 1 program_name
FROM sys.dm_exec_sessions
AND login_name = 'sa_Public'
ORDER BY login_time DESC
) IN ( 'OOXX平台' )
ROLLBACK ;
END ;
这样子?
--限制IP访问数据库
CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS 'testuser'
FOR LOGON
AS
BEGIN
--使用testuser这个帐号的连接
IF ORIGINAL_LOGIN() = 'testuser'
AND
--以下是判断非本地的,你可以不使用
( SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE [host_name] <> 'localhost'
AND original_login_name = 'testuser'
) > 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 = 'testuser'
ORDER BY login_time DESC
) NOT IN ( '192.100.168.149', '192.100.168.179' )
ROLLBACK ;
END ;