I'm trying to get a logon trigger to return a custom error. Thus far everything I have tried causes it to return the standard error:

enter image description here

My trigger looks like this:

CREATE TRIGGER Test_Logon_Trigger

IF ORIGINAL_LOGIN()= 'login_test' 
        raiserror('Custom Error',25,16) 
        --PRINT 'Print'

I have tried printing an error message and using a RAISERROR command separately and in combination. I've also tried using the WITH NOWAIT clause on RAISERROR. In all cases it just sends the message to the error log. Does anyone know how to modify the error being returned displayed on the screen? I'm even ok with returning a second error if necessary.

  • Do you still want the login to succeed, or are you trying to prevent certain logins from logging in even though they can log in successfully? And you just want to display a custom message so that they don't see that their log in was caught in a trigger? Apr 4, 2014 at 1:30
  • Specifically I was thinking the trigger would prevent the user from logging in but return the reason why to them. For example if I want to restrict users from using a sql login from any application other than the specific application it was created for I want it to return the error: "You are not allowed to use this login." If however they are using the wrong workstation I want it to say "You are not allowed to log in from this workstation." Apr 4, 2014 at 1:54
  • Gotcha. I don't believe there's any way to influence or override what is displayed in that SSMS UI. Apr 4, 2014 at 2:03
  • How about the other way? Is there a way to print something on the screen after they have logged in? In say a query window? Apr 4, 2014 at 2:11
  • Don't think so - the logon trigger has no idea about the query window context (if it does exist). This is similar to trying to raise a msgbox or similar - the processes are more detached than you think. Apr 4, 2014 at 11:25

1 Answer 1


This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.)

If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified exists. If it told you why, this makes penetrating the system much easier because there's less guesswork involved.

So really if there is a way to do this using built-ins, I would have no hesitation to report it as a bug.

That of course doesn't solve what you're trying to do, which, IMO, is questionable for the same reasons.

Since login failures should be rare, what I would suggest is that all the restrictions be published internally in a predictable location (e.g., SharePoint). The main things with this are to make sure the list stays up-to-date (it could be data-driven), and also that it's visible to everyone who has access to the server (as opposed to everyone who is able to attempt to connect to the server, which is very different).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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