Monitoring Server Logins and Using Server Triggers in SQL Server
It is crucial to ensure that you are not unintentionally blocking all users from connecting to your SQL Server instance. Proper monitoring helps maintain accessibility and security.
One useful way to monitor login activity and server events is through server-level triggers. Below, we explore how to check existing server triggers and create a logon auditing mechanism.
Checking Server Triggers
To see what server triggers are currently active on your SQL Server, run the following query:
SELECT * FROM sys.server_triggers;
This will provide details about all server-level triggers configured on your instance.
Creating a Logon Auditing Table and Trigger
You can audit login events by creating a table to store logon details and a server trigger that captures login activity.
Step 1: Create a Logon Auditing Table
CREATE TABLE LogonAuditing
(
SessionId INT,
LogonTime DATETIME,
HostName VARCHAR(50),
ProgramName VARCHAR(500),
LoginName VARCHAR(50),
ClientHost VARCHAR(50),
eveData XML
);
GO
This table will record essential information about each login session.
Step 2: Create a Logon Audit Trigger
CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @TrgData XML = EVENTDATA();
BEGIN TRY
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'LogonAuditing')
BEGIN
INSERT INTO [dbo].[LogonAuditing] (SessionId, LogonTime, HostName, ProgramName, LoginName, ClientHost)
SELECT
@SPID,
@TrgData.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME'),
HOST_NAME(),
APP_NAME(),
@TrgData.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(50)'),
@TrgData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'VARCHAR(50)');
END
END TRY
BEGIN CATCH
-- Handle errors silently or log if necessary
END CATCH
END;
This trigger records login events and their details into the LogonAuditing table each time a login occurs.
Additional Resources
For more in-depth exploration on using tables instead of triggers, managing multiple triggers, understanding object dependencies, and working with assemblies in SQL Server, check out these articles by Himanshu Patel:
