SQL 限制账号登入时间

使用触发器来限制账号是否能登入

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