Home / server / Questions / 90748
Accepted
user7078
Asked: 2009-12-04 10:23:10 +0800 CST

How do I disable trigger that killed SQL access?

  • 772

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/f9236131-cfcc-48ed-b431-57884842e874/

I needed to limit access to Sql Mgt Studio for certain users. I found the article above that creates a trigger to prevent making connections for specified users. 

CREATE TRIGGER [SQLGP_Logon_trigger_sql_tools]

ON ALL SERVER FOR LOGON

AS

BEGIN

  IF ORIGINAL_LOGIN() not in ('domain\Administrator',
    'sa',
    'domain\user1',
    'domain\user2',
    'domain\user3') AND 
    (program_name() like 'Microsoft SQL Server Management Studio' 
    or program_name() like 'Microsoft SQL Server Management Studio - Query' 
    or program_name() like 'SQL Query Analyzer')
  ROLLBACK

END

As I was playing with the code on our development server I created a trigger that prevents access for all users. I tried using osql to connect but get the error message

Logon failed for login 'someuser' due to trigger execution.

This is similar to the message you get when connecting with Sql Mgt Studio.

Is there a way to force this trigger to disable?

sql-server-2005

1 Answers

  1. Best Answer

    Connect to the admin connection. In SSMS type admin:servername\instance as server name. Usually the DAC connection is enabled only locally, so you should try it from the server host. If that fails too (I can't remeber if DAC runs the logon trigger) there is always the server administrative single user startup sqlservr -m

    • 3