项目场景:
創建登入觸發器失誤,導致所有用戶都無法等入數據庫,
我的本意是想創建一個限制用戶連接 IP 和連接客戶端應用程序的登入觸發器,然後在觸發的時候寫入一行日誌記錄
问题描述:
錯誤案例 :
alter TRIGGER [tr_connection_limit]
ON ALL SERVER WITH EXECUTE AS ‘sa’
FOR LOGON
AS
BEGIN
IF (
EXISTS(
SELECT 1 FROM sys.dm_exec_sessions
WHERE
session_id = EVENTDATA().value(’(/EVENT_INSTANCE/SPID)[1]’, ‘smallint’)
AND( program_name like ‘%SQL%’
or EVENTDATA().value(’(/EVENT_INSTANCE/ClientHost)[1]’, ‘varchar(15)’) <> ‘10.130.69.168’
)
)
AND ORIGINAL_LOGIN() = ‘test_user’
)
insert into testdaily…trigger_log values (ORIGINAL_LOGIN(),
EVENTDATA().value(’(/EVENT_INSTANCE/EventType)[1]’, ‘NVARCHAR(100)’),
EVENTDATA().value(’(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘NVARCHAR(2000)’) )
ROLLBACK;
END;
原因分析:
錯誤的原因是 如