sql server logon trigger custom message

Heisenberg 261 Reputation points
2023-03-17T15:49:03.3+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,623 questions
0 comments No comments
{count} votes

1 additional answer

Sort by: Most helpful
  1. Zahid Butt 556 Reputation points
    2023-03-17T16:23:50.7433333+00:00