Logon Trigger

The Logon triggers are Execute/fired only after the successful authentication,
before the user session is actually established. 
or we can say it is executed automatically in response to a LOGON event.
If authentication is failed then the logon triggers will not be fired.

Why we Need it?
1) Track the Login Activity
2) Restrict login 

Drop Table If Exists AuditTable

CREATE TABLE AuditTable

(

AuditTableID INT IDENTITY PRIMARY KEY,

SessionId int,

LogonTime datetime,

        HostName varchar(50),

        ProgramName varchar(500),

        LoginName varchar(50),

        ClientHost varchar(50)

)

Go

Create TRIGGER LogonAudit

ON ALL SERVER 

FOR LOGON

AS

BEGIN

  DECLARE @LogonTrigger xml,

      @EventTime datetime,

      @LoginName varchar(50),

      @ClientHost varchar(50),

      @LoginType varchar(50),

      @HostName varchar(50),

      @AppName varchar(50)

 

  SET @LogonTrigger= EventData() 

  SET @EventTime = @LogonTrigger.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

  SET @LoginName = @LogonTrigger.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')

  SET @ClientHost = @LogonTrigger.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')

  SET @HostName = HOST_NAME()

  SET @AppName = APP_NAME()

  INSERT INTO practice.dbo.AuditTable

  ( 

    SessionId,

    LogonTime,

    HostName,

    ProgramName,

    LoginName,

    ClientHost

  )

  VALUES

  (

    @@spid,

    @EventTime,

    @HostName,

    @AppName,

    @LoginName,

    @ClientHost

  )

END

GO

Use Master

Go

Create LOGIN sqllogin WITH PASSWORD = '32487^%^%$#!@hdgfgsdfjh'

Use [practice]

Go

create USER sqllogin FROM LOGIN sqllogin;

Go

Use [practice]

Go

GRANT SELECT ON dbo.VW_empDept TO sqllogin

GRANT INSERT ON dbo.AuditTable TO sqllogin

connect to SQL Server using sqllogin account

Select * from practice.dbo.AuditTable

Restrict login 

Create TRIGGER LogonTriggersRestrict

ON ALL SERVER 

FOR LOGON

AS

BEGIN

DECLARE @LogonTrigger xml, 

      @LoginName varchar(100)

SET @LogonTrigger= EventData() 

SET @LoginName = @LogonTrigger.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')

  

  IF @LoginName = 'sqllogin'

  BEGIN

    PRINT 'User ' + @LoginName + ' is blocked by Sagar'

    ROLLBACK

  END

END

connect to SQL Server using sqllogin account

exec  sp_readerrorlog

Real life scenario:
 
Logon Trigger available on server, but
due to some activity not able to connect with server in this case below error
will show,
 
Logon failed for login 'sa' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
 
How to Resolve it?
We have an option to connect with SQL server and drop that trigger and Login as a regular way.
 
To connect to a server using the DAC (Dedicated Administrator Connection
)
open SSMS - SQL Server management studio,
-- File - New - Database Engine Query - shown in image 1,
ADMIN:<SQL SERVER Name> as in Server Name
- shown in image 2
Connect.

New SQL Query Window will open,

Use master
Select * from sys.server_triggers

You will get list of log on triggers at here, just delete it,

DROP TRIGGER <trigger Name> ON ALL SERVER
Connect as from Normal User.