Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
ON ALL SERVER 
FOR LOGON
AS
BEGIN

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

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.

share|improve this question
    
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? –  Aaron Bertrand Apr 4 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." –  Kenneth Fisher Apr 4 at 1:54
    
Gotcha. I don't believe there's any way to influence or override what is displayed in that SSMS UI. –  Aaron Bertrand Apr 4 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? –  Kenneth Fisher Apr 4 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. –  Aaron Bertrand Apr 4 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).

share|improve this answer

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.