MSSQL账号+IP限制登录限制触发器

发布时间：2023-05-09 13:16:34 所属栏目：MsSql教程来源：

导读：MSsql账号+IP限制登录限制触发器
USE [master]
GO
/****** Object: DdlTrigger [tr_connection_blacklist] Script Date: 2021/12/24 10:10:39 ******/
DROP TRIGGER [tr_connection_blacklist] ON ALL SERVE

MSsql账号+IP限制登录限制触发器
USE [master]
GO
/****** Object: DdlTrigger [tr_connection_blacklist] Script Date: 2021/12/24 10:10:39 ******/
DROP TRIGGER [tr_connection_blacklist] ON ALL SERVER
GO
/****** Object: DdlTrigger [tr_connection_blacklist] Script Date: 2021/12/24 10:10:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [tr_connection_blacklist]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR logoN
AS
BEGIN
declare @ip nvarchar(max)
declare @sessionId int
declare @loginName nvarchar(max)
declare @clientProgram nvarchar(max)
select @ip= eventdata().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)')
select @sessionId= eventdata().value('(/EVENT_INSTANCE/SPID)[1]','int')
select @loginName= eventdata().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')
select @clientProgram=a.program_name from master.sys.dm_exec_sessions a where a.session_id=@sessionId
--满足下列条件，禁止登录
IF(
           --sa账号登录黑名单：
           @loginName = 'sa' and @ip in ('192.168.5.39','192.168.5.40')
       --运维账号登录白名单：运维安全账号登录IP白名单

           @loginName = 'sa' AND (@clientProgram like 'Navicat%' or @clientProgram like 'sqlDbx%')
       --运维账号登录白名单：运维安全账号登录IP白名单

           @loginName = 'yunwei1' AND @ip not in ( '192.168.5.39','192.168.118.70')
       --开发账号白名单：开发安全账号登录IP白名单

           @loginName = 'dev_user1' and @ip not in( '192.168.5.40' )
ROLLBACK;
ENABLE TRIGGER [tr_connection_blacklist] ON ALL SERVER

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!