Login trigger control IP and user logon log logging

Use [logon_db]
GO

/****** object:table [dbo]. [Logonlog] Script DATE:2016/9/10 17:24:11 ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

SET ansi_padding on
GO

CREATE TABLE [dbo]. [Logonlog] (
[ID] [int] IDENTITY (*) Not NULL,
[session_id] [smallint] Null
[Login_time] [DateTime] Null
[HOST_NAME] [nvarchar] (+) NULL,
[Original_login_name] [nvarchar] (+) NULL,
[Client_net_address] [varchar] () NULL,
[Xmlevent] [XML] NULL,
[Blocked] [smallint] Null
CONSTRAINT [Pk_logonlog1] PRIMARY KEY CLUSTERED
(
[Id] ASC
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY] textimage_on [PRIMARY]

GO

SET ansi_padding OFF
GO

Use [logon_db]
GO

/****** object:table [dbo]. [Validlogon] Script DATE:2016/9/10 17:24:07 ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo]. [Validlogon] (
[ID] [int] IDENTITY (*) Not NULL,
[LoginName] [sysname] Not NULL,
[Validip] [nvarchar] () not NULL,
CONSTRAINT [Pk_validlogon] PRIMARY KEY CLUSTERED
(
[Id] ASC
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]

GO

--Insert test data
Insert [dbo].[ Validlogon] ([LoginName], [Validip]) VALUES (n ' sa ', n ' 192.168.1.200 ')
INSERT [dbo].[ Validlogon] ([LoginName], [Validip]) VALUES (n ' heqianjin-pc\administrator ', n ' 127.0.0.1 ')
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 ')

--Create a login trigger
--=========================== ==================
--Author: < Listen to the wind and rain;
-Create Date: <2013.05.21>
--Description: < login and IP filtering, Support IP range specification,
--Blog: --=========================================== = =
CREATE TRIGGER [Tr_logon_checklogon_rangeip]
on all SERVER with EXECUTE as ' sa '
for logon
as

BEGIN
DECLARE @LoginName sysname
DECLARE @IP NVARCHAR (15)
DECLARE @ValidIP NVARCHAR (15)
DECLARE @len INT
DECLARE @data XML
DECLARE @blocked BIT;

SET @len = 0
SET @blocked = 0
SET @LoginName = Original_login ();
SET @data = EVENTDATA ();
SET @IP = @data. Value (' (/event_instance/clienthost) [1] ', ' NVARCHAR (15) ');

--Determine login name and IP
IF not EXISTS (SELECT [Validip] from [logon_db].[ DBO]. [Validlogon] WHERE [LoginName] = @LoginName and [Validip] = @IP)
BEGIN
--whether there is an IP range match
SET @ValidIP = (SELECT TOP 1 [Validip] from [logon_db].[ DBO]. [Validlogon]
WHERE [LoginName] = @LoginName and [Validip] like '%[*] ');
--Replace the IP of the client if it exists
IF (CHARINDEX (' * ', @ValidIP) > 0 and @IP <> ' <local machine> ' and @IP <> ' 127.0.0.1 ')
BEGIN
DECLARE @SubValidIP NVARCHAR (15)
SET @SubValidIP = SUBSTRING (@ValidIP, 0,charindex (' * ', @ValidIP))
SET @len = Len (@SubValidIP) + 1
IF (SUBSTRING (@IP, 0, @len)! = @SubValidIP)
BEGIN
ROLLBACK;
SET @blocked = 1
END
END
ELSE
BEGIN
ROLLBACK;
SET @blocked = 1
END
END

--Log records
INSERT into [logon_db]. [dbo]. [Logonlog]
([session_id]
, [Login_time]
, [HOST_NAME]
, [Original_login_name]
, [client_net_address]
, [Xmlevent]
, [Blocked])
SELECT
@data. Value (' (/event_instance/spid) [1] ', ' smallint '),
GETDATE (),
@data. Value (' (/event_instance/servername) [1] ', ' sysname '),
@data. Value (' (/event_instance/loginname) [1] ', ' sysname '),
@data. Value (' (/event_instance/clienthost) [1] ', ' NVARCHAR (15) '),
@data, @blocked
END;

----above is just a record of a person's work study, for reference only, all things are from the Internet, if there is infringement on the right of the film please contact me to delete.

---Please test the problem, the consequences at your own risk.

----If you run this trigger, you can't log in.

---can run the database with the Sqlcmd-f parameter, and then use osql to connect the database, disable the trigger. (Disable MSSQL service when running in sqlcmd)

-----M is not good!!!!

-F

Login trigger control IP and user logon log logging