服务器级登录触发器限制登录：需记录数据且允许正常登录

咱们先直接说核心问题：你的触发器执行时出错了，而SQL Server的登录触发器有个硬性规则——只要触发器运行抛出错误，就会直接拒绝登录请求。

最可能的出错原因 #

你触发器里要插入数据到[master].[dbo].[IP_AND_HOST]表，但大概率这个表你还没创建！插入一个不存在的表肯定会报错，触发器一报错，所有登录请求就都被拦截了，包括sa在内。

除此之外还有几个次要的潜在问题：

• 没加SET NOCOUNT ON：触发器执行时返回的行数可能干扰登录流程
• 没有错误捕获机制：哪怕是系统视图临时访问异常这类小问题，都会触发登录失败
• 没有限定查询范围：你现在关联了所有连接和会话，万一登录瞬间会话还没完全建立，join语句可能出问题

先解决紧急问题：恢复登录权限 #

现在所有人都登不上，得先把触发器禁用或者删掉，步骤如下：

1. 停止SQL Server服务
2. 打开命令提示符，找到SQL Server的安装目录（比如C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn），执行：

   sqlservr.exe -m
   

   这会让SQL Server以单用户模式启动，此时只能有一个连接。
3. 打开另一个命令提示符，用sqlcmd连接：

   sqlcmd -S .
   

4. 执行禁用触发器的命令：

   DISABLE TRIGGER Logon_Trigger_HOST_IP ON ALL SERVER;
   GO
   

   或者直接删掉：

   DROP TRIGGER Logon_Trigger_HOST_IP ON ALL SERVER;
   GO
   

5. 关闭单用户模式的命令窗口，重启SQL Server服务，现在就能正常登录了。

修复触发器，实现登录记录且不阻止登录 #

现在咱们重新做一个靠谱的触发器，步骤如下：

1. 先创建目标记录表 #

首先得把你要插入的表建好，不然还是会出错：

CREATE TABLE [master].[dbo].[IP_AND_HOST] (
    ID INT IDENTITY(1,1) PRIMARY KEY, -- 加个自增主键方便管理
    SPID INT,
    IPAddress VARCHAR(100),
    MachineName VARCHAR(100),
    ApplicationName VARCHAR(200),
    LoginName VARCHAR(100),
    LoginTime DATETIME DEFAULT GETDATE() -- 加上登录时间，审计更有用
);
GO

2. 创建带错误处理的触发器 #

这次咱们加上错误捕获，确保哪怕记录失败，登录也能正常进行：

CREATE TRIGGER Logon_Trigger_HOST_IP ON ALL SERVER FOR LOGON
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回行数干扰登录流程
    BEGIN TRY
        -- 只记录当前登录的会话，避免查询所有连接，效率更高
        INSERT INTO [master].[dbo].[IP_AND_HOST]
        SELECT 
            conn.session_ID as SPID,
            conn.client_net_address as IPAddress,
            sess.host_name as MachineName,
            sess.program_name as ApplicationName,
            sess.login_name as LoginName
        FROM sys.dm_exec_connections conn
        INNER JOIN sys.dm_exec_sessions sess 
            ON conn.session_ID = sess.session_ID
        WHERE conn.session_ID = @@SPID;
    END TRY
    BEGIN CATCH
        -- 这里可以选择记录错误到另一个日志表，或者什么都不做
        -- 比如：INSERT INTO [master].[dbo].[Trigger_Error_Log] (ErrorMsg, OccurTime) VALUES (ERROR_MESSAGE(), GETDATE());
    END CATCH
END
GO

修复点说明 #

• SET NOCOUNT ON：防止触发器返回的“影响行数”信息干扰登录过程
• TRY...CATCH：捕获所有执行错误，哪怕插入失败也不会抛出错误阻止登录
• WHERE conn.session_ID = @@SPID：只查询当前正在登录的会话，避免查询所有连接，既高效又不会插入无关数据

现在再测试登录，所有用户都能正常登录，同时登录信息会被记录到IP_AND_HOST表里啦。

内容的提问来源于stack exchange，提问作者code.bot