I'm trying to create a logon trigger to prevent certain users from connecting to the primary node. This is what I have:
USE MASTER
GO
CREATE TRIGGER tr_Prevent_BA_UTS_Primary_Connections
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF sys.fn_hadr_backup_is_preferred_replica('UTS') = 0
BEGIN
IF ORIGINAL_LOGIN() = 'TriggerTest1'
BEGIN
DECLARE @ErrorText [varchar](128)
SET @ErrorText = 'Unable to connect to primary'
PRINT @ErrorText
ROLLBACK;
END
END
END
GO
For some reason this prevents all users from logging onto the instance instead of just preventing the user specified.
Am I missing something here?