SQL Server LOGON triggers are a type of DDL trigger that are activated in response to a LOGON event. These triggers allow you to perform certain actions or enforce certain security measures when a user attempts to log into a SQL Server instance.

LOGON triggers can be used to audit the logon events and also to prevent or restrict the users from logging into the SQL Server instance based on certain conditions.

It's important to keep in mind that LOGON triggers are executed in the context of the SQL Server service account, so it should be granted with the necessary permissions to execute the code inside the trigger.

Here is an example of a LOGON trigger that is activated when a user attempts to log into the SQL Server instance:

CREATE TRIGGER Logon_Restriction
ON ALL SERVER
AFTER LOGON
AS
BEGIN
IF SUSER_NAME() = 'testuser'
BEGIN
RAISERROR('Access denied', 16, 1);
ROLLBACK;
END
END;

This trigger is activated AFTER LOGON on the ALL SERVER which means it will be triggered for any user trying to connect to the SQL Server. It checks the name of the user attempting to log in using the SUSER_NAME() function. If the user's name is "testuser", the trigger raises an error message "Access denied" and rolls back the logon attempt.

RAISERROR is a T-SQL statement that raises an error with a user-defined message.
ROLLBACK statement is used to undo any changes made by the previous transaction.