create_time datetime 创建时间_SQL Server账号控制（控制某些IP登录时间和某些账号的登录方式）...

一、应用场景

1.控制A账号上班时间不允许从某些段的IP进行登录数据库。

2.控制B账号登录数据库只能使用某些程序进行登录。

3.记录所有账号的登录来源。(根据需要可以不用)

注意：此方法，一定要在本地经过严格测试后再使用。

二、实现方式。

1.创建测试数据库。

CREATE DATABASE TestDB

2.创建Config用于控制账号的配置。

CREATE TABLE [dbo].[Config](

[LoginName] [sysname] NOT NULL,

[Type] [VARCHAR](50) NOT NULL,

[Value] [NVARCHAR](50) NOT NULL,

[IsEnabled] [BIT] NOT NULL,

PRIMARY KEY CLUSTERED

(

[LoginName] ASC,

[Type] ASC,

[Value] ASC,

[IsEnabled] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

3.配置说明 ：

1.)B账号只能使用下面的程序连接数据库。

Red Gate Software%

Microsoft SQL Server Management Studio%

dbForge SQL Complete%

2.)控制A账号从84,85段IP连接数据库的访问。

4.创建表ServerLoginLog用于记录登录信息。

CREATE TABLE [dbo].[ServerLoginLog](

[LogID] [BIGINT] IDENTITY(1,1) NOT NULL,

[SPID] [SMALLINT] NOT NULL,

[LoginName] [sysname] NOT NULL,

[ClientHost] [NVARCHAR](200) NOT NULL,

[ClientHostName] [NVARCHAR](200) NULL,

[ClientProgramName] [NVARCHAR](200) NOT NULL,

[LoginType] [sysname] NOT NULL,

[EventType] [sysname] NULL,

[XmlEvent] [XML] NOT NULL,

[ServerName] [NVARCHAR](200) NOT NULL,

[SID] [NVARCHAR](200) NOT NULL,

[PostTime] [DATETIME2](3) NOT NULL,

[CreateTime] [DATETIME2](3) NOT NULL,

PRIMARY KEY CLUSTERED

(

[LogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

5.在实例上创建触发器记录账号的登录信息。

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [trg_connection_limit]

ON ALL SERVER

FOR LOGON

AS

BEGIN

DECLARE @data XML;

DECLARE @spidOfHostName NVARCHAR(100)='';

DECLARE @spidOfProgramName NVARCHAR(100)='';

DECLARE @spid SMALLINT=0;

SET @data = EVENTDATA();

SET @spid= @data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT');

SELECT @spidOfHostName=dess.HOST_NAME

,@spidOfProgramName=dess.program_name

FROM sys.dm_exec_sessions dess(NOLOCK)

LEFT JOIN sys.dm_exec_requests der(NOLOCK) ON der.session_id=dess.session_id

WHERE dess.session_id=@spid

INSERT INTO [ServerLog].[dbo].[ServerLoginLog] ([SPID],[SID],[ClientHost],[ClientHostName],[LoginName]

,[LoginType],[EventType],[XmlEvent],[ServerName],[ClientProgramName],[PostTime],[CreateTime])

VALUES

(

@data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT'),

@data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(200)'),

@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(200)'),

ISNULL(@spidOfHostName,''),

@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),

@data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname'),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),

@data,

@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),

ISNULL(@spidOfProgramName,''),

@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),

GETDATE()

);

END

GO

ENABLE TRIGGER [trg_connection_limit] ON ALL SERVER

GO

6.在ServerLoginLog中创建表触发器，当登录的账号不满足相关测试时进行回滚不允许登录。

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

/*日志登陆初发器*/

CREATE TRIGGER [dbo].[trg_ServerLoginLog_insert] ON [dbo].[ServerLoginLog]

AFTER INSERT

AS

BEGIN

DECLARE @LoginName NVARCHAR(200);

DECLARE @ClientProgramName NVARCHAR(200);

DECLARE @ClientHost NVARCHAR(200);

SELECT @LoginName = ied.LoginName ,

@ClientProgramName = ied.ClientProgramName ,

@ClientHost = ClientHost

FROM Inserted ied;

IF @LoginName = 'sa'

OR @LoginName = 'Admin'

BEGIN

IF NOT EXISTS ( SELECT *

FROM Config cf

WHERE cf.LoginName = @LoginName

AND cf.[Type] = 'allow_client_program_name'

AND cf.IsEnabled = 1

AND @ClientProgramName LIKE cf.Value )

BEGIN

PRINT 'Login信息:[' + @LoginName + ']帐号使用的客户端是['

+ @ClientProgramName + ']';

ROLLBACK;

END;

END;

IF EXISTS ( SELECT *

FROM Config cf

WHERE cf.LoginName = @LoginName

AND cf.[Type] = 'not_allow_ip'

AND cf.IsEnabled = 1

AND cf.Value = @ClientHost )

BEGIN

PRINT 'Login信息:[' + @LoginName + ']帐号使用的客户端IP是[' + @ClientHost

+ ']';

ROLLBACK;

END;

END;

GO

7.启用启用访问策略.

WITH t AS(

SELECT * FROM Config cf WHERE cf.LoginName='Ttest'

) UPDATE t SET IsEnabled=1; ---启用访问策略(0时禁用范围策略)

8.验证策略是否生效。

1.)策略启用后，账号登录的时候会出现如下的错误。

2.)验证使用某些程序登录。

连接参数中随便设定App=aa,进行登录，出现如下的错误。

3.)使用允许的程序进行连接，便可以登录。

设定APP=Microsoft SQL Server Management Studio%