USE [master]
GO
--禁止访问的IP
CREATE TABLE [dbo].[ForbiddenIP](
[IP] [nvarchar](15) NOT NULL,
[说明] [nvarchar](50) NULL,
[设定时间] [datetime] NULL,
CONSTRAINT [PK_ForbiddenIP] PRIMARY KEY CLUSTERED
(
[IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ForbiddenIP] ADD CONSTRAINT [DF_ForbiddenIP_设定时间] DEFAULT (getdate()) FOR [设定时间]
GO
--外网登陆记录表
CREATE TABLE [dbo].[loginlog](
[loginame] [varchar](30) NULL,
[ipaddress] [varchar](40) NULL,
[spid] [int] NULL,
[hostname] [varchar](30) NULL,
[logtime] [datetime] NULL
) ON [PRIMARY]
GO
--访问记录触发器
CREATE trigger [tr_login] on all server WITH EXECUTE AS 'sa'
for logonas
declare @loginame varchar(30),
@ipaddress varchar(30),
@spid int,
@hostname varchar(30);
select @loginame = eventdata().value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@ipaddress = eventdata().value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname'),
@spid = eventdata().value('(/EVENT_INSTANCE/SPID)[1]', 'int');
if ((substring(@ipaddress,1,7)<>'192.168') and (@ipaddress not in ('127.0.0.1', 'servername')))
begin
select @hostname = [host_name] from sys.dm_exec_sessions where session_id = @spid;
begin try
insert into master.dbo.loginlog(loginame,ipaddress,spid,hostname, logtime)
values( @loginame,@ipaddress,@spid,@hostname, getdate() );
end try
begin catch
print '写入日志错误'
end catch
end
GO
--访问IP限制触发器
CREATE TRIGGER [tr_LoginCheck]
ON ALL SERVER
FOR LOGON
AS
declare @IP varchar(15);
set @ip=EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(15)');
IF Exists (select * from ForbiddenIP where ip=@ip)
ROLLBACK TRAN
GO