Simple answer: You can't.
sql server logon trigger custom message

Heisenberg
201
Reputation points
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
Accepted answer
-
1 additional answer
Sort by: Most helpful
-
Zahid Nadeem 361 Reputation points
2023-03-17T16:23:50.7433333+00:00 Hi,
You need to use Try..Catch , may get help from below links:
Regards,
-