DBA Scripts: Create Login Activity Session

Peter Whyte

–

Part of the DBA-Tools Project.

“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
    SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = STOP;
    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,
        '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:

Example Output

session_name output_file max_file_mb max_files total_capacity_mb session_lifetime status
LoginActivity D:\SQLTrace\LoginActivity.xel 100 6 600 3 days (MAX_DURATION) RUNNING

Real output captured against a local SQL Server 2025 (v17) instance. remove_cmd isn’t shown in the table above (it’s a long DDL string), but the script returns it as a ninth column, the exact ALTER EVENT SESSION ... STOP; DROP EVENT SESSION ... ON SERVER; needed to tear this session down. On SQL Server 2025 and later that column shows a real day count, 3 days (MAX_DURATION) here, because the session will stop itself. On older versions it reads manual stop (pre-2025) instead, since MAX_DURATION doesn’t exist yet and the session runs until you stop it.

Understanding the Results

  • output_file is where the rolling .xel files land. SQL Server appends its own sequence and timestamp suffix to the base name once the session is running, so the file you’ll actually find on disk looks like LoginActivity_0_<timestamp>.xel, not the bare path shown here.
  • max_file_mb × max_files is a disk budget, not a time budget. 100MB × 6 files here is 600MB of rolling capacity; how many days that buys you depends entirely on how much login traffic the server sees.
  • session_lifetime tells you whether this will clean itself up. Trust it, if it says manual stop, put a reminder somewhere to come back and stop it.
  • remove_cmd is generated for you and safe to copy straight into a query window, or use the dedicated Remove XE Session script to find and remove it (and any other DBA-created session) later.

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.

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

How do I see who is connecting to a SQL Server instance?

Extended Events is the practical answer. This script sets up a session that captures the sqlserver.login event with the caller’s hostname, application, and account name, then you query the rolling output files with T-SQL. Server-side audit and the default trace can do something similar, but Extended Events is lighter and easier to query directly.

Does an Extended Events login trace slow the server down?

Not meaningfully. Extended Events is built to have low overhead, that’s the entire design point over the older SQL Trace. Capturing just login and failed-login events, as this session does, is a tiny fraction of the server’s overall event volume.

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.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *