Logon Trigger

Introduction

The Logon Trigger is used to catch the successful login entry by both Windows Authentication and SQL Server Authentication. By the use of logon triggers we can audit or control server sessions by  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. That means that trigger logon will not fire if authentication fails.

This article is to understand the use of Logon Trigger.

To understand it properly let’s take an example.

Example-1

By this example we can actually set a Logging Audit feature.

-- Create The Audit Database --

CREATE DATABASE AUD_Db

GO

USE AUD_Db

GO

-- Create Audit Table --

IF OBJECT_ID(N'dbo.tbl_LOGINAUD', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_LOGINAUD;

   END

CREATE TABLE dbo.tbl_LOGINAUD

(

   LoginUser VARCHAR(512),

   AppName   VARCHAR(max),

   DBUser    VARCHAR(512),

   SPID      INT,

   LogonTime DATETIME)

GO

-- Create Logon Trigger --

IF OBJECT_ID(N'dbo.trg_SQLLogon', N'TR') IS NOT NULL

   BEGIN

      DROP TRIGGER AUD_Db.dbo.trg_SQLLogon;

   END

GO  

CREATE TRIGGER AUD_Db.dbo.trg_SQLLogon

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO AUD_Db.dbo.tbl_LOGINAUD

       (LoginUser, AppName, DBUser, SPID, LogonTime)

SELECT SYSTEM_USER, APP_NAME, USER, @@SPID, GETDATE();

END

GO

Example -2

For this we have to care a User in the name of test1

Now we create the Trigger

IF OBJECT_ID(N'dbo.trg_ConnectRestric', N'TR') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[trg_ConnectRestric];

   END

GO  

CREATE TRIGGER [dbo].[trg_ConnectRestric]

ON ALL SERVER

FOR LOGON

AS

BEGIN

DECLARE @ErrorTxt VARCHAR(128);

SET @ErrorTxt = 'Cannot allow login to "test1" outside of Business hours. ';

SET @ErrorTxt = @ErrorTxt + 'Please try again between business hours 10:00 and 18:00.';

IF ORIGINAL_LOGIN() = 'test1' AND

    (DATEPART(HOUR, GETDATE()) < 10 OR DATEPART (HOUR, GETDATE()) > 18)

    BEGIN

        PRINT @ErrorTxt;

        ROLLBACK;

    END

END

GO

Hope you like it.