sql server logon trigger custom message

Question

hi folks,

I m creating a logon trigger to restrict any SQL login from a particular host. Following script is working as expected however its not flashing message as mentioned in the RAISERROR. When logon is denied the message user get is "logon failed for login 'xxx' due to trigger execution". Any idea how can i flash custom message?

CREATE TRIGGER [trigger_1] 
ON ALL SERVER
FOR LOGON
AS
BEGIN
	IF
	((
	left(host_name(),5) in ('XXX')
	)
	and 
	 (SELECT name FROM sys.server_principals  WHERE TYPE = 'S' and name = ORIGINAL_LOGIN()) is not null)
	BEGIN
		RAISERROR('Please login using windows authentication, SQL Authentication is refrained from workstation', 25, 1);
		ROLLBACK;
	END
END

Answer 1

Simple answer: You can't.

Answer 2

Hi,

You need to use Try..Catch , may get help from below links:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver16

Regards,