Logon Trigger有问题导致无法登入

问题：Trigger有问题导致无法登入SQL SERVER

情况：

想要针对每个登入资料库的帐号做纪录，首先先建一个纪录用Table取名 ServerLogonHistory
在网站上找到一个Logon trigger

USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [Connections]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGON ASBEGININSERT INTO ServerLogonHistorySELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()ENDGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOENABLE TRIGGER [Connections] ON ALL SERVERGO

结果一用.... 全部帐号都无法登入 一个傻眼

登入名称'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
我的老天鹅啊啊啊啊

处理程序

--找出triggerselect * from sys.triggersselect * 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