I have created a logon trigger in my SQL Server 2019 instance.
The idea is to have both:
Sample trigger is:
CREATE OR ALTER TRIGGER [trigger_name]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
BEGIN TRAN
INSERT INTO LOG_TABLE ...
COMMIT TRAN
IF ORIGINAl_LOGIN() = 'TEST2'
BEGIN
ROLLBACK;
END;
END
I test connecting with users TEST1 and TEST2.
Test:
Problem: no log written for TEST2
You can write the log after you rollback the trigger's transaction. EG
CREATE OR ALTER TRIGGER [only_sa_allowed]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAl_LOGIN() <> 'sa'
BEGIN
ROLLBACK;
END;
INSERT INTO LOG_TABLE(user_name) values (ORIGINAL_LOGIN());
END