SQL 限制账号登入时间

dew

使用触发器来限制账号是否能登入
1. 创建一张表来保存非法时间登入的账号信息

CREATE TABLE [dbo].[sys_login_blocked_log] (
[Id] int IDENTITY(1, 1) NOT NULL,
[session_id] smallint NULL,
[login_time] datetime NULL,
[host_name] nvarchar(128) COLLATE Chinese_PRC_CI_AS NULL,
[original_login_name] nvarchar(128) COLLATE Chinese_PRC_CI_AS NULL,
[client_net_address] varchar(48) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_LogonLog] PRIMARY KEY CLUSTERED ([Id])
)
ON [PRIMARY]
GO


2. 创建限制登入触发器,
   账号 test  登入时间限制为7-17点无法登入

CREATE TRIGGER [tr_logon_CheckTime] ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()='test' AND DATEPART(hh,GETDATE()) BETWEEN 7 AND 17
BEGIN
ROLLBACK;
INSERT INTO [mig_bug].[dbo].[sys_login_blocked_log]
([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;
GO