SQL Server登录触发器问题：public权限用户登录失败，需捕获登录时间

问题根源 #

错误17892的核心原因是：仅拥有public服务器角色的登录账户，在触发LOGON触发器时，没有权限执行触发器内的两项操作：

1. 访问sys.dm_exec_sessions需要VIEW SERVER STATE服务器级权限，public角色默认无此权限；
2. 访问db_admin.dbo.tblUser需要该数据库的访问权限及对表的SELECT、UPDATE权限，public用户默认也无此权限。

同时原触发器存在逻辑冗余：无需通过sys.dm_exec_sessions查询登录时间，LOGON触发器触发时的当前时间即为本次登录时间。

解决方案 #

步骤1：优化触发器逻辑 #

先简化触发器，直接使用当前时间作为登录时间，并添加SET NOCOUNT ON避免返回额外结果集影响登录流程：

CREATE OR ALTER TRIGGER LogonTimeStamp
ON ALL SERVER FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    -- 直接取触发时刻作为登录时间，无需查询DMV
    DECLARE @time DATETIME = GETDATE();

    IF EXISTS (SELECT 1 FROM db_admin.dbo.tblUser WHERE name = SYSTEM_USER)
    BEGIN
        UPDATE db_admin.dbo.tblUser
        SET lastLoginDate = @time 
        WHERE name = SYSTEM_USER;
    END
END;

步骤2：通过模块签名赋予触发器必要权限（推荐，安全无权限扩散） #

模块签名允许触发器以特定权限执行，无需给public用户额外权限：

1. 在master库创建证书

USE master;
GO
CREATE CERTIFICATE LogonTriggerCert
ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere' -- 替换为安全密码
WITH SUBJECT = 'Permissions for Logon Time Tracking Trigger',
EXPIRY_DATE = '2030-12-31';
GO

2. 从证书生成登录名并授予权限

-- 创建服务器登录名
CREATE LOGIN LogonTriggerLogin FROM CERTIFICATE LogonTriggerCert;
GO

-- 授予访问db_admin库及表的权限
USE db_admin;
GO
CREATE USER LogonTriggerUser FROM LOGIN LogonTriggerLogin;
GRANT SELECT, UPDATE ON dbo.tblUser TO LogonTriggerUser;
GO

3. 用证书给触发器签名

USE master;
GO
ADD SIGNATURE TO LogonTimeStamp BY CERTIFICATE LogonTriggerCert
WITH PASSWORD = 'YourStrongPasswordHere'; -- 与创建证书时的密码一致
GO

备选方案：使用EXECUTE AS OWNER（简单但需注意安全） #

若不想使用证书，可让触发器以所有者身份执行，需确保触发器所有者拥有足够权限：

CREATE OR ALTER TRIGGER LogonTimeStamp
ON ALL SERVER FOR LOGON
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @time DATETIME = GETDATE();

    IF EXISTS (SELECT 1 FROM db_admin.dbo.tblUser WHERE name = SYSTEM_USER)
    BEGIN
        UPDATE db_admin.dbo.tblUser
        SET lastLoginDate = @time 
        WHERE name = SYSTEM_USER;
    END
END;

注意：触发器所有者需具备db_admin库的访问权限，以及dbo.tblUser的SELECT、UPDATE权限。

内容的提问来源于stack exchange，提问作者TLew79