As a SQL Server developer, I recently came across an interesting phenomenon while working on a security auditing project for one of my clients. The requirement was to record all successful logins on the servers. To achieve this, I decided to create logon triggers. Little did I know that executing a logon trigger multiple times would result in unexpected behavior!
Let’s take a closer look at the example I encountered:
/* 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
GOIn the above example, I created an audit database, a table to store the logon history, and a logon trigger to capture successful logins. However, when I checked the audit table, I noticed multiple entries and different process IDs.
This peculiar behavior led me to a conclusion – similar actions like logging into the server took place multiple times. This raised a few questions:
1. Have you ever experienced this kind of situation?
2. If yes, did you receive similar entries?
If you haven’t encountered this phenomenon yet, I encourage you to recreate the situation by running the above script. Once you’ve done that, feel free to share your observations and conclusions in the comments section below. I’m eager to learn more about the cause behind these multiple entries.
Valid suggestions and explanations will be published on this blog with due credit. Let’s unravel the mystery together!