As the name implies, logon triggers fire in response to the logon event that is raised when a user sessions is being established. The logon triggers can be used to audit and control server sessions, such as tracking the login activity, restricting logins to SQL Server, or limiting or denying the number of login sessions for a specific user. The logon triggers always run after the authentication phase, but before the user session is actually established, which means that trigger logon will not fire if authentication fails. Therefore, all messages originating inside the trigger logon, which would usually reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.

For example, the following code shows how logon trigger rejects attempts to logon for “testuser1”, if they are initiated outside business hours i.e. between 10:00 and 18:00 hours.

First step of the process is to create SQL user called “testuser1” on the server, To do so execute the following code:

USE [master]
GO

--Create the login on your servel called "testuser1"
CREATE LOGIN [testuser1]
	WITH PASSWORD = N'StrongPassword'
		,DEFAULT_DATABASE = [master]
		,DEFAULT_LANGUAGE = [us_english]
		,CHECK_EXPIRATION = OFF
		,CHECK_POLICY = OFF
GO

Now create logon trigger called “connection_limit_trigger”, which only permits login attempts for “testuser1” during business hours i.e. between 10:00 and 18:00 hours.

USE [master]
GO

CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
FOR LOGON AS

BEGIN
	DECLARE @ErrorText [varchar] (128)

	SET @ErrorText = 'Cannot allow login to "testuser1" outside of normal business hours. '
	SET @ErrorText = @ErrorText + 'Please try again between business hours 10:00 and 18:00.'

	IF ORIGINAL_LOGIN() = 'testuser1'
		AND (
			DATEPART(HOUR, GETDATE()) < 10
			OR DATEPART(HOUR, GETDATE()) > 18
			)
	BEGIN
		PRINT @ErrorText

		ROLLBACK;
	END
END;
GO

ENABLE TRIGGER [connection_limit_trigger]
	ON ALL SERVER
GO

Now that we have configured our logon trigger, the “testuser1” will not be able to log on to SQL Server outside the specified business hours of 10:00 to 18:00. The following dialog box will appear if you attempt to login to SQL Server with “testuser1” SQL login:

If you review the SQL Server error logon, you will see that it has an entry about this failed login attempt containing the information message from our logon trigger:

One interesting observation about logon trigger, which you can see from above example, is that the logon triggers generates multiple entries for successful and failed logon attempts.

Conclusion

The logon on triggers are useful to track and control SQL Server login activity. The information captured by logon trigger helps us identify and prevent unauthorised access from SQL Server.