登录触发器

在 SQL Server 中构建与优化触发器

Florin Angelescu

Instructor

定义与属性

触发后执行一组操作
针对 LOGON 事件执行
发生在身份验证之后、会话建立之前

登录触发器前提条件

触发事件
触发器说明
触发器名称

LOGON
审计服务器登录成功/失败
LogonAudit

登录触发器定义

CREATE TRIGGER LogonAudit

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS
    INSERT INTO ServerLogonLog
                        (LoginName, LoginDate, SessionID, SourceIPAddress)
    SELECT ORIGINAL_LOGIN(), GETDATE(), @@SPID, client_net_address
    FROM SYS.DM_EXEC_CONNECTIONS WHERE session_id = @@SPID;

登录触发器定义小结

CREATE TRIGGER LogonAudit
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
    INSERT INTO ServerLogonLog
                        (LoginName, LoginDate, SessionID, SourceIPAddress)
    SELECT ORIGINAL_LOGIN(), GETDATE(), @@SPID, client_net_address
    FROM SYS.DM_EXEC_CONNECTIONS WHERE session_id = @@SPID;

让我们来练习！

在 SQL Server 中构建与优化触发器

Preparing Video For Download...