問題：Trigger有問題導致無法登入SQL SERVER

情況：

想要針對每個登入資料庫的帳號做紀錄，首先先建一個紀錄用Table取名 ServerLogonHistory
在網站上找到一個Logon trigger

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Connections]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
INSERT INTO ServerLogonHistory
SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Connections] ON ALL SERVER
GO

結果一用.... 全部帳號都無法登入 一個傻眼

登入名稱'sa'的登入因觸發程序執行而失敗
已將資料庫內容變更為'master'
已將語言設定變更為繁體中文 (Microsoft SQL Server，錯誤: 17892)

啊~ 沒想到這麼快就把資料庫弄爆

GOOGLE一下，可以用cmd下sqlcmd方式去drop trigger

C:\Users\XXX>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Connections ON ALL SERVER
2> GO

還是不行啦 死都不讓我進去就是了

繼續請教GOOGLE大神，發現了DAC這個神奇的東西
SQL Dedicated Administrative Connection (DAC)

MSDN定義DAC：
SQL Server 為系統管理員提供了特殊的診斷連接，可在伺服器的標準連接失效時使用。這個診斷連接可讓系統管理員存取 SQL Server 以執行診斷查詢和排解疑難問題，即使 SQL Server 未回應標準連接要求。

使用方法： 進SSMS，伺服器名稱敲 admin:Your_Server\Instance，再輸入帳密

阿咧~ 不行，仔細看了一下網路大神的說法

你應該用【新增查詢】右邊的【資料庫引擎查詢】(Database Engine)，而非Object Explorer 上的 Connection。

再試一次

進來了！趕快把有問題的trigger drop掉

這時候就可以用Object Explorer 上的 Connection登入了
進來囉！！！

查看一下原因...
原來是沒有指定Table的DB害Trigger找不到
Trigger一失敗，就會整個RollBack所以所有帳號都被卡死了

結論： 用Trigger真的要小心謹慎再小心 還有心臟要夠大顆(不對

參考資料：
Identify SQL Server databases that are no longer in use
https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/
SQL Dedicated Administrative Connection (DAC) 專用管理員連接
https://blog.xuite.net/tolarku/blog/30615763-SQL+Dedicated+Administrative+Connection+%28DAC%29+%E5%B0%88%E7%94%A8%E7%AE%A1%E7%90%86%E5%93%A1%E9%80%A3%E6%8E%A5
SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.
https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/comment-page-3/#comments

----- 10/16 補充

昨天將SQL SERVER服務重開後又遇到一樣的問題(這個既視感....)
這次我沒有新增觸發程序，結果又出現因為觸發程序LOGON FAIL
我的老天鵝啊啊啊啊

處理程序

1. 利用DAC進入查詢頁面
2. 找出所有觸發程序

--找出trigger
select * from sys.triggers
select * from sys.server_triggers

一開始在sys.triggers裡面甚麼都沒有
還以為系統中邪了(誤
後來才看到還有sys.server_trigger
裡面塞了一個我半年前在測試logon用到的trigger
因為昨天對這個trigger裡面使用的table做異動
想當然爾就又死給我看囉

trigger 使用真的要小心小心再小心啊

參考資料
Logon failed for login due to trigger execution
https://dba.stackexchange.com/questions/218811/logon-failed-for-login-due-to-trigger-execution