执行登录触发器后，登录sql server报错： 由于执行触发器，登录'xxx \ Administrator'登录失败。已将数据库上下文更改为master

背景：

在cmd中执行sqlcmd命令：

sqlcmd -S WIN-FI7RNDQ5SB4\MYSQLINSTANCE -i mysql.sql

其中mysql.sql内容如下：

CREATE LOGIN test WITH PASSWORD = '输入密码'
GO

ALTER TRIGGER tr_connection_limit

ON ALL SERVER
FOR LOGON
AS
BEGIN


IF ORIGINAL_LOGIN()= 'test'

AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.191.22')
     ROLLBACK;
END;

执行结果是：

C:\Users\Administrator>sqlcmd -S WIN-FI7RNDQ5SB4\MYSQLINSTANCE -i C:\Temp\sqltest\SQLQuery.sql
Sqlcmd：错误：用于SQL Server的Microsoft ODBC驱动程序13：由于执行触发器，登录'WIN-FI7RNDQ5SB4 \ Administrator'登录失败
结果使用SSMS登录Sql Server时失败，无论是用windows验证方式登录还是sql server账号登录都会报错：
由于执行触发器，登录'WIN-FI7RNDQ5SB4 \ Administrator'登录失败

我的解决方法是：

步骤一：用sqlcmd删除登录触发器：

C:\Users\Administrator>sqlcmd -A -S WIN-FI7RNDQ5SB4\MYSQLINSTANCE[服务器名]
1> select * from sys.server_trigger_events
2> go

object_id   type      type_desc                 
   is_trigger_event is_first is_last event_group_type event_group_type_desc  
-------------------------------- ---------------- -------- --
-------------------------------------------------------------------
 2107154552         147 LOGON   
  1        0       0             NULL NULL 
(1 行処理されました)

1> select * from sys.server_triggers
2> go

name     
    object_id   parent_class parent_class_desc      parent_id   type type_desc  
 create_date      modify_date       is_ms_shipped is_disabled
---------------------------------------------------------------------------------------------------
tr_connection_limit     2107154552          100 SERVER   
  0 TR   SQL_TRIGGER      
2018-10-23 17:40:04.367 2018-10-24 11:29:30.647     0     0

(1 行処理されました)
(1 行処理されました)

1> drop trigger tr_connection_limit[登录触发器名] on all server;
2> go
1> select * from sys.server_triggers
2> go

name    
 object_id   parent_class parent_class_desc  parent_id   type type_desc  
create_date             modify_date       is_ms_shipped is_disabled
--------------------------------------------------------------------------

(0 行処理されました)
1>

接下来登录Sql Server就成功了，无论是用什么账户登录。

步骤二：修改mysql.sql内容如下：

USE [master]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
 

CREATE LOGIN test WITH PASSWORD = '输入密码'
GO

ALTER TRIGGER tr_connection_limit

ON ALL SERVER
FOR LOGON
AS
BEGIN


IF ORIGINAL_LOGIN()= 'test'

AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.191.22')
     ROLLBACK;
END;

这次再运行就不会出现登录失败的问题了。