Logon Triggers: SQL Server

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.

About these ads

6 thoughts on “Logon Triggers: SQL Server

  1. OOps sorry for the typo – I meant “Are Logon triggers bad for SQL Server Performance of a Production System?”. If so, is there any alternative to track every open connection(After the authentication and before the session is actually established)

    Like this

  2. I am just trying to capture all Logon details to a table. Nothing else is in the Logon trigger code. It just inserts details of the logged in user to a table.

    Like this

  3. Hi Basit, When I created a trigger on a table, I am able to see it inside the trigger object under my table. Where can I see my logon trigger according to your code above?

    Thank you

    Like this

  4. Hi Basit,

    Its a good article and helpful. I’ve developed similar kind of trigger with the database mail. The email will be sent when a one particular user trying to access the server, but don’t know why its not working out.

    Can you please help me where I went wrong.

    Create TRIGGER connection_limit_trigger
    ON ALL SERVER
    FOR LOGON
    AS
    BEGIN
    IF ORIGINAL_LOGIN()= ‘ABC’ AND( (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = ‘ABC’)=1)

    EXEC msdb.dbo.sp_send_dbmail
    @recipients = ‘ABC@emailprovider.com’,
    @body = ‘Successfully Login’,
    @subject = ‘Automated Success Message’ ;
    END;

    Thanks,
    Harish

    Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s