一、只允许指定IP访问数据库
创建测试账号CREATE LOGIN testuser WITH PASSWORD = '123'
GO
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.50.145','192.168.8.138')
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
===========================================================================================
二、限制角色只能运行指定IP访问
--创建测试数据库
USE MASTER
GO