如何为各数据库创建专属登录记录触发器并仅记录该库登录信息？

步骤1：在每个目标数据库创建统一的登录审计表 #

先在你要监控的12个数据库中分别创建结构一致的审计表，用于存储对应库的登录信息（每个库单独执行以下脚本）：

CREATE TABLE dbo.LoginAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    SessionID INT NOT NULL,
    ApplicationName NVARCHAR(128) NOT NULL,
    LoginName NVARCHAR(128) NOT NULL,
    DatabaseName NVARCHAR(128) NOT NULL,
    LoginTime DATETIME NOT NULL DEFAULT GETDATE(),
    ClientIP NVARCHAR(48) NULL
);

步骤2：创建服务器级LOGON触发器 #

创建一个服务器级登录触发器，在用户登录时触发，获取会话关联的默认数据库信息，仅将该会话的登录数据插入到对应数据库的审计表中：

CREATE TRIGGER tr_ServerWideLoginAudit
ON ALL SERVER
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    -- 排除系统会话（SPID≤50为SQL Server系统进程）
    IF @@SPID > 50
    BEGIN
        DECLARE @DatabaseName NVARCHAR(128),
                @SessionID INT,
                @ApplicationName NVARCHAR(128),
                @LoginName NVARCHAR(128),
                @ClientIP NVARCHAR(48),
                @InsertSQL NVARCHAR(MAX);

        -- 获取当前会话的核心信息
        SELECT 
            @SessionID = s.session_id,
            @ApplicationName = s.program_name,
            @LoginName = s.login_name,
            @DatabaseName = DB_NAME(s.database_id),
            @ClientIP = c.client_net_address
        FROM sys.dm_exec_sessions s
        JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
        WHERE s.session_id = @@SPID;

        -- 仅在你的12个目标数据库列表内执行插入操作
        -- 替换下方括号内的名称为你实际的12个数据库名
        IF @DatabaseName IN ('DB1', 'DB2', 'DB3', 'DB4', 'DB5', 'DB6', 'DB7', 'DB8', 'DB9', 'DB10', 'DB11', 'DB12')
        BEGIN
            -- 动态构建插入语句，避免SQL注入并确保插入到对应库
            SET @InsertSQL = N'INSERT INTO ' + QUOTENAME(@DatabaseName) + N'.dbo.LoginAudit
                                (SessionID, ApplicationName, LoginName, DatabaseName, ClientIP)
                                VALUES (' + CAST(@SessionID AS NVARCHAR(10)) + N', 
                                        N''' + REPLACE(@ApplicationName, '''', '''''') + N''', 
                                        N''' + REPLACE(@LoginName, '''', '''''') + N''', 
                                        N''' + REPLACE(@DatabaseName, '''', '''''') + N''', 
                                        ' + CASE WHEN @ClientIP IS NULL THEN 'NULL' ELSE N'N''' + REPLACE(@ClientIP, '''', '''''') + N'''' END + N')';

            EXEC sp_executesql @InsertSQL;
        END
    END
END
GO

关键说明 #

• 权限要求：创建触发器的账户需要ALTER ANY SERVER TRIGGER服务器权限，同时需拥有每个目标数据库的INSERT权限。
• 防注入处理：使用QUOTENAME和REPLACE函数处理特殊字符，避免动态SQL引发注入风险。
• 系统会话过滤：通过@@SPID > 50排除SQL Server自身的后台会话，避免无效记录。

修正后的登录信息查询脚本 #

你提供的查询存在语法错误（多余的*），修正后的脚本可查看当前活跃会话的数据库连接信息：

SELECT 
    DB_NAME(p.dbid) AS [Database],
    c.session_id,
    s.program_name,
    s.login_name
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
JOIN sys.sysprocesses p ON p.spid = c.session_id
ORDER BY c.session_id;

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