Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Just a friendly note, you should accept Remus Rusanu's answer if it is the one that worked for you (click the checkmark next to the answer). Cheers :) –  squillman Dec 3 '09 at 19:06

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
THANK YOU! I wound up doing both. I put the service into single user mode, then used SQLCMD -A to connect to the DAC. I could then disable the trigger and put everything back. –  user7078 Dec 3 '09 at 18:49
    
ty squillman. I voted up but forgot to accept. I started messing with different permutations of this resolution. Running in single user mode is not required, just using the Dedicated Admin Console locally on the server. –  user7078 Dec 4 '09 at 20:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.