--创建test登录账号

 CREATE LOGIN test WITH PASSWORD = 'test'

 GO

 --创建登录触发器

 ALTER TRIGGER [restrict_login]

 --控制 数据库引擎使用sa用户帐户来验证对模块引用的对象的权限,是借用，仅在此存储过程中有用

 ON ALL SERVER WITH EXECUTE AS 'sa'

 FOR LOGON

 AS

 BEGIN

 --返回连接到 SQL Server 实例的登录名，并校验

 IF ORIGINAL_LOGIN()= 'test'

 AND

 --返回有关服务器或数据库事件的信息。校验登陆事件的IP是否为指定IP

 (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))

 NOT IN('','192.168.0.108','192.168.0.109')

 --在隐式或显式调用 EVENTDATA 的事务提交或回滚之后，EVENTDATA 所返回的数据将无效。

      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 db22

 GO

 CREATE TABLE dbo.connectip ( 

     IP NVARCHAR(20), 

     CONSTRAINT PK_IP PRIMARY KEY CLUSTERED(IP) 

 ); 

 GO

 --插入白名单IP

 USE db22

 GO

 INSERT INTO dbo.connectip(IP) VALUES('');

 INSERT INTO dbo.connectip(IP) VALUES('192.168.0.108');

 --创建登录触发器

 CREATE TRIGGER [restrict_login_check] 

 ON ALL SERVER 

 FOR LOGON 

 AS 

 BEGIN

 --指示 SQL Server 登录名是否为指定服务器角色的成员

     IF IS_SRVROLEMEMBER('sysadmin') = 1

     BEGIN 

         DECLARE @IP NVARCHAR(20); 

         SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(20)')); 

         IF NOT EXISTS(SELECT IP FROM [db22].dbo.connectip WHERE IP = @IP) 

         ROLLBACK;

     END;

 END;

如果想把这个表内的ip都加密，结果发现不一致导致登录失败了，怎么都进不来，怎么办？按照以下步骤来：

1.打开Sql Server Manage Studio，不要登录（反正是会失败），去菜单：“文件”--“新建”--“使用当前连接的查询”，在打开的登录窗口中，服务器名称输入：ADMIN:127.0.0.1

，账号sa + 原密码，，点击登录，成功

2.在打开的查询界面执行：drop trigger tr_logon_CheckIP on all server   执行成功，就删除掉这个触发器了

3.重新登录sa，发现可以进去了

3）限制某个login（比如testtime）只能在某个时间段登录，同时记录非法登录信息

假设一个场景，我们规定某些用户只能晚上固定某个时间段登录服务器进行一些报表拉取操作，而白天不允许其登录，那么此例可以帮助我们实现而且能达到监控的目的。

代码：

 --创建登录账号

 CREATE LOGIN testnight WITH PASSWORD = 'testnight'

 GO

 --创建拦截日志表

 USE db22

 GO

 CREATE TABLE dbo.login_record ( 

     [Id] INT IDENTITY(1,1),

     [session_id] SMALLINT,

     [login_time] DATETIME,

     [host_name] NVARCHAR(128),

     [original_login_name] NVARCHAR(128),

     [client_net_address] VARCHAR(48),

     CONSTRAINT PK_id PRIMARY KEY CLUSTERED(Id) 

 ); 

 --创建登录触发器

 ALTER TRIGGER [restrict_login_record]

 ON ALL SERVER WITH EXECUTE AS 'sa'

 FOR LOGON

 AS

 BEGIN

     IF ORIGINAL_LOGIN()='testnight' AND

     DATEPART(hh,GETDATE()) BETWEEN 9 AND 18

     BEGIN

         ROLLBACK;

         INSERT INTO [db22].[dbo].[login_record]

             ([session_id]

             ,[login_time]

             ,[host_name]

             ,[original_login_name]

             ,[client_net_address])

         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

             WHERE a.session_id = @@SPID

     END;

 END;

此例中，如果testnight用户在白天9:00-18:00任意时间登录，则会抛出如下登录错误

同时会在拦截日志表dbo.login_record中有如下记录：

甚至我们可以模仿mysql里的mysql.user表建立一个含host,user字段的登录白名单表，用来保存用户与IP对应关系，这样就可以对所有用户进行管控了，其实它的实现也很简单，主要是一个取IP的方法：EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)')，然后与白名单表里的IP和user做比对，若白名单表里没有对应项，则ROLLBACK并记录登录信息到log表即可，这里就不赘述了，有兴趣的朋友可以试试。