Logon trigger in SQL Server by serverku

As a secure part, recently I worked with login details and the auditing for the same. I needed to capture each event for logging statistics like login name, the time when logon, the program through established connection, session and host or client IP. This event is raised when user sessions make connections with SQL Server instances where we configured LOGON trigger. This will helpful for us to make auditing the logon details for each connections. For the small demo let us create required objects and implement it.

USE DemoDB
GO
-- Creating audit table
CREATE TABLE LogonAuditing
(
 SessionId int,
 LogonTime datetime,
 HostName varchar(50),
 ProgramName varchar(500),
 LoginName varchar(50),
 ClientHost varchar(50)
)
GO

USE Master
GO
-- Creating DDL trigger for logon
CREATE TRIGGER LogonAuditTrigger
 ON ALL SERVER 
FOR LOGON
AS
BEGIN
       DECLARE @LogonTriggerData xml,
                     @EventTime datetime,
                     @LoginName varchar(50),
                     @ClientHost varchar(50),
                     @LoginType varchar(50),
                     @HostName varchar(50),
                     @AppName varchar(500)
                     
       SET @LogonTriggerData = eventdata()
  
    SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       SET @HostName = HOST_NAME()
       SET @AppName = APP_NAME()--,program_name()
 
 INSERT INTO DemoDB.dbo.LogonAuditing
  (
  SessionId,
  LogonTime,
  HostName,
  ProgramName,
  LoginName,
  ClientHost
 )
 SELECT 
  @@spid,
  @EventTime,
  @HostName,
  @AppName,
  @LoginName,
  @ClientHost

END
GO

Audit table and the trigger to fill that table is created. Now it is time to evaluate, test it and review the audit table.

We have all details of the logging events from the table. Also one more interesting thing is as we can prevent unwanted user logins and connections to SQL Server. This prohibition can be for the login, program or host, for that we just need to add some code in logon trigger for conditions to make rollback at that time like following,

-- Preventing 'sa' login
IF @LoginName = 'sa'
BEGIN
ROLLBACK;
END
  
-- Preventing the connections from SSMS
IF @AppName = 'Microsoft SQL Server Management Studio'
BEGIN
ROLLBACK;
END

For first criteria to prevent login ‘SA’, if connection made for the same and the message fired at the time of event logging,

You can make your criteria as per requirement as I implemented for login and program connections. Are you using a Logon Trigger?