SQL Server如何限制IP登陆：登陆触发器的运用(3)

现在时间是17:20左右，我使用nightworker登陆数据库，这违反了登陆触发器：tr_logon_CheckTime，所以提示Figure5的错误信息，并且在LogonBlockedLog拦截日志表中出现了一条记录，这个表可以帮助我们更好的了解登陆账号的登陆信息。

（Figure5：nightworker用户登陆错误信息）

（Figure6：拦截日志表）

(四) 我们进一步模仿MySQL的mysql.User表的用法，用表保存用户与IP的对应关系，这样就可以对所有登陆用户进行控制了。

--Script5：
--登陆名与有效IP对应表
USE Logon_DB
GO
CREATE TABLE [dbo].[ValidLogOn](
    [Id] INT IDENTITY(1,1) NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [ValidIP] [nvarchar](15) NOT NULL,
    CONSTRAINT [PK_ValidLogOn] PRIMARY KEY CLUSTERED ([Id])
 )
--创建唯一约束索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_ValidLogOn_LV] ON [dbo].[ValidLogOn] 
(
    [LoginName] ASC,
    [ValidIP] ASC
)
 --插入测试数据
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'BARXXX\Administrator', N'<local machine>')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'<local machine>')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'192.168.1.48')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'<local machine>')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'127.0.0.1')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'192.168.1.48')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'<local machine>')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.120')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.48')
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.50')
--创建登录触发器
-- =============================================
-- Author:        <听风吹雨>
-- Create date:    <2013.05.21>
-- Description:    <限制登陆名和IP>
-- Blog:        <http://www.cnblogs.com/gaizai />
-- =============================================
CREATE TRIGGER [tr_logon_CheckLogOn]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName sysname
    DECLARE @IP NVARCHAR(15)
    SET @LoginName = ORIGINAL_LOGIN();
    SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); 
    --判断登录名和IP
    IF NOT EXISTS(SELECT [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) 
        ROLLBACK;
END;

（Figure7：登陆名与有效IP对应表）

用户登陆名与IP对应关系表[ValidLogOn]，有几点需要注意的，BARXXX\Administrator这个是Windows 身份验证中操作系统的帐号，你需要根据你的实际情况进行修改；IP当中你则需要注意<local machine>和127.0.0.1这些特殊的地址，我个人还是建议在这个表中加入这些信息的。