Have you ever wondered why a logon trigger in SQL Server fires multiple times for a single login? In a previous article, I shared an interesting observation about this behavior. If you haven’t read it yet, I recommend checking out the article titled “Interesting Observation of Logon Trigger On All Servers” before continuing with this post.

In that article, I posed the question of why a logon trigger fires multiple times for a single login, when it should ideally fire only once. I received several answers from the community, both in the comments section and in my MVP private news group. Today, let’s discuss the answer to this question.

The answer lies in the fact that multiple SQL Server services are running and IntelliSense is turned on. To verify this, let’s run the following script to create a database and a logon audit table:

/* Create Audit Database */
CREATE DATABASE AuditDb
GO

USE AuditDb
GO

/* Create Audit Table */
CREATE TABLE ServerLogonHistory (
    SystemUser VARCHAR(512),
    DBUser VARCHAR(512),
    SPID INT,
    LogonTime DATETIME
)
GO

/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon ON ALL SERVER FOR LOGON AS
BEGIN
    INSERT INTO AuditDb.dbo.ServerLogonHistory
    SELECT SYSTEM_USER, USER, @@SPID, GETDATE()
END
GO

After executing the script, disable all the other SQL Server services as shown in the image below using SQL Server Configuration Manager. This step is crucial to ensure that only one login attempt is made.

Now, try to log in to the system one more time. Check the audit table again to verify if there is a single entry for the single login.

I would like to express my gratitude to all of you for your active participation and for providing wonderful suggestions and answers. Your insights have been invaluable in understanding this behavior.

Feel free to share your opinion on this observation. Have you encountered similar situations in your SQL Server environment? Let’s continue the discussion in the comments section below.