DBA Scripts: Create Login Activity Session

“Who actually connects to this server?” is a question you can’t answer from documentation. Not who’s supposed to connect, who is. Ownership changes, connection strings get copied into three other applications nobody told you about, and a service account keeps authenticating from a box that was decommissioned eighteen months ago. None of that shows up until you go looking.

SQL Server doesn’t log successful logins anywhere useful by default. The error log can be made to do it, but that’s noisy, unstructured, and fills fast. This script creates a lightweight Extended Events session instead: low overhead, rolling files, and every login (successful or failed) captured with the hostname, application, and account behind it, ready to query with T-SQL.

Why Login Activity Session Matters

A DBA making a decision about a server, who can safely be cut off, whose access needs revoking, whether a migration missed a connection string, needs ground truth about who’s actually connecting, not who’s supposed to be. Without it:

• Decommissioning decisions get made on assumption, and a forgotten batch job or reporting tool finds out the hard way when the server disappears.
• Security reviews can’t tell you whether a service account is authenticating from an unexpected host, or whether something is still connecting as sa.
• A migration’s login and firewall list gets built from memory instead of evidence, and something inevitably gets left off.

When to Run This Script

• Before decommissioning or retiring a server, run it for several days first to catch batch jobs and end-of-period activity
• As part of a security review, to confirm who’s connecting and from where
• Before a migration, to build an accurate list of every account and application that needs to keep working on the new instance
• Any time you’re asked “is anything still using this?” and the honest answer is “I don’t know”

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Create-LoginActivitySession
Category    : traces
Purpose     : Creates a lightweight Extended Events session that captures every successful and failed login to the server — who, from where, and using which application.
              Use to answer "who connects to this server" before decommissioning, during security reviews, or to baseline connection patterns.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-create-login-activity-session/)
Requires    : ALTER ANY EVENT SESSION, VIEW SERVER STATE
*/
-- SAFE:CreatesObjects
-- IMPACT:Low
SET NOCOUNT ON;

/* ── Configuration — edit these before running ───────────────────────────── */
DECLARE @SessionName NVARCHAR(128) = N'LoginActivity';
DECLARE @TraceFolder NVARCHAR(260) = N'D:\SQLTrace';
    /* Folder must exist; SQL Server service account needs write access. */
DECLARE @MaxFileMB INT = 100; /* max size per .xel file in MB */
DECLARE @MaxFiles INT = 6; /* number of rollover files; oldest is overwritten first */
DECLARE @RetentionDays INT = 3;
    /* SQL Server 2025+ (v17+) only: session auto-stops after this many days.
       On older versions this has no effect — the session runs until you stop
       it manually (ALTER EVENT SESSION ... ON SERVER STATE = STOP). */
/* ─────────────────────────────────────────────────────────────────────────── */

DECLARE @ProductMajorVersion INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);

/* Escape any single-quotes in the path so the dynamic SQL string stays valid */
DECLARE @FilePathRaw NVARCHAR(260) = @TraceFolder + N'\' + @SessionName + N'.xel';
DECLARE @FilePathEsc NVARCHAR(522) = REPLACE(@FilePathRaw, N'''', N'''''');

DECLARE @sql NVARCHAR(MAX);

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = @SessionName)
BEGIN
    /* STOP only if it is actually running - ALTER ... STATE = STOP on an already
       stopped session raises Msg 25704 and prints a red error over a successful run */
    IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = @SessionName)
    BEGIN
        SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = STOP;';
        EXEC sp_executesql @sql;
    END;
    SET @sql = N'DROP EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER;';
    EXEC sp_executesql @sql;
    PRINT 'Existing session ' + @SessionName + ' stopped and dropped.';
END;

/* Build WITH clause — MAX_DURATION is SQL Server 2025+ (v17+) only */
DECLARE @WithClause NVARCHAR(500) =
    N'WITH (
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    STARTUP_STATE = ON';
IF @ProductMajorVersion >= 17
    SET @WithClause = @WithClause
        + N',
    MAX_DURATION = ' + CAST(@RetentionDays AS NVARCHAR(5)) + N' DAYS';
SET @WithClause = @WithClause + N'
)';

SET @sql = N'
CREATE EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER
ADD EVENT sqlserver.login (
    ACTION (
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.database_name
    )
    WHERE sqlserver.is_system = 0
),
ADD EVENT sqlserver.error_reported (
    /* error 18456 = Login failed; state field indicates the specific reason */
    ACTION (
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.database_name
    )
    WHERE error_number = 18456
)
ADD TARGET package0.event_file (
    SET filename = N''' + @FilePathEsc + N''',
        max_file_size = ' + CAST(@MaxFileMB AS NVARCHAR(10)) + N',
        max_rollover_files = ' + CAST(@MaxFiles  AS NVARCHAR(10)) + N'
)
' + @WithClause + N';
ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = START;';

BEGIN TRY
    EXEC sp_executesql @sql;

    SELECT
        @SessionName AS session_name,
        @FilePathRaw AS output_file,
        @MaxFileMB AS max_file_mb,
        @MaxFiles AS max_files,
        @MaxFileMB * @MaxFiles AS total_capacity_mb,
        CASE WHEN @ProductMajorVersion >= 17
             THEN CAST(@RetentionDays AS VARCHAR(5)) + ' days (MAX_DURATION)'
             ELSE 'manual stop (pre-2025)'
        END AS session_lifetime,
        'RUNNING' AS status,
        'IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = ' + QUOTENAME(@SessionName, '''') + ') ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + ' ON SERVER STATE = STOP; DROP EVENT SESSION ' + QUOTENAME(@SessionName) + ' ON SERVER;' AS remove_cmd;

    PRINT 'Session ' + @SessionName + ' created and started.';
    PRINT 'Output file : ' + @FilePathRaw;
    PRINT 'View data   : SSMS > Management > Extended Events > Sessions > ' + @SessionName + ' > right-click target > View Target Data';
END TRY
BEGIN CATCH
    PRINT 'ERROR creating session: ' + ERROR_MESSAGE();
    THROW;
END CATCH;

This drops and recreates the session if one of the same name already exists, then builds and starts a server-scoped Extended Events session that captures the sqlserver.login event (filtered to non-system logins) and sqlserver.error_reported filtered to error 18456 (login failed), writing both to a rolling .xel file target.

Before you run this: @TraceFolder defaults to D:\SQLTrace, and that folder has to exist and be writable by the SQL Server service account before the session will start. Create the folder first, or edit @TraceFolder at the top of the script to point at a folder that already exists on your instance.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Create (or safely recreate) the rolling login-activity trace:
.\run.ps1 Create-LoginActivitySession

# To run against a remote sql server:
.\run.ps1 Create-LoginActivitySession -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/traces/Create-LoginActivitySession.sql
• powershell/wrappers/traces/Create-LoginActivitySession.ps1

Example Output

One confirmation row, and the numbers are the point: 600MB of rolling capacity and a three day lifetime, deliberately lighter than the decommission audit session because this one captures connections rather than every batch and RPC call. Note there is no database_filter column here, logins are a server-level event and are never scoped to one database.

Understanding the Results

Best Practices

• The trace folder must exist and be writable before you run this, that’s the one precondition the script can’t work around for you.
• Size @MaxFileMB and @MaxFiles to the login volume you actually expect. A busy application server can burn through 600MB of rolling capacity in hours, overwriting the very evidence you were trying to capture.
• Run this for long enough to see the full pattern, several business days at minimum, so you catch batch jobs and end-of-period activity that a single day would miss.
• This captures hostnames, application names, and login identities. Treat the output the same as any other audit evidence, not something to leave lying around indefinitely.

Microsoft’s reference covers sp_executesql and sys.server_event_sessions in full.

Related Scripts

You may also find these scripts useful:

• Create Decommission Audit Session
• Create SP Execution Session
• Get Active XE Sessions
• Get XE Session Activity
• Remove XE Session
• Extended Events & Tracing (hub), the cluster this sits under
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

You can’t make a good decision about who’s allowed to connect, or safe to cut off, without knowing who’s actually connecting. This script gets that evidence on disk with almost no overhead, and the confirmation row it returns tells you exactly how long you have and exactly how to remove it when you’re done.

Stand it up before any decommission, security review, or migration where “who’s still using this” is the question that actually matters.