At a customer, the use of one shared SQL login with sysadmin level rights had started to get a bit out of hand. Multiple people used it, nobody was 100% sure from where, and “we’ll fix it later” had quietly turned into “we should really have fixed this months ago”.

The goal was actually pretty simple:

1. Find out who is using that login
2. Capture enough context to make the data useful
3. Stop using one shared admin login
4. Move to named personal admin access instead

That last bit matters. A “personal sa account” sounds convenient, but it is usually better to move to named personal accounts, ideally Windows or Entra backed, and only grant sysadmin where it is truly needed.

Extended Events turned out to be a very practical way to start building the evidence.

TL;DR

If one shared SQL admin login is being used by too many people, Extended Events is a very practical way to get facts first.

Use a small XE session with login and logout, write to event_file, capture host and app context, and keep the filter tight to that one login.

If event_file fails, test with ring_buffer first, then check folder permissions for the SQL Server service account.

When the .xel files start piling up, the pain is usually in reading them all back, not in the live capture itself.

And once you know who is actually using the shared login, use that evidence to move toward named personal admin access.

Why XE?

I could have gone for DMV snapshots only, or SQL Server Audit only, or even a logon trigger if I was feeling adventurous.

But XE sits in a nice sweet spot.

It is light enough to leave running for a while, flexible enough to capture the extra context you actually care about, and easy to write to .xel files for later analysis.

The context I wanted was:

• login name
• host name
• application name
• session id
• optional Windows context
• enough history to see patterns

Beside the flexibilty and usability, I have been drilled by XE fan-boy Peter Kruis to use them, even to clean the dishes! So, here we go ;-)

First working version

This was the first useful version of the session:

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'Track_SharedAdmin_LoginUsage')
    DROP EVENT SESSION [Track_SharedAdmin_LoginUsage] ON SERVER;
GO

CREATE EVENT SESSION [Track_SharedAdmin_LoginUsage]
ON SERVER
ADD EVENT sqlserver.login
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.server_principal_name,
        sqlserver.session_id,
        sqlserver.username,
        sqlserver.nt_username
    )
    WHERE
    (
        sqlserver.server_principal_name = N'shared_sa'
        OR sqlserver.username = N'shared_sa'
    )
),
ADD EVENT sqlserver.logout
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.server_principal_name,
        sqlserver.session_id,
        sqlserver.username,
        sqlserver.nt_username
    )
    WHERE
    (
        sqlserver.server_principal_name = N'shared_sa'
        OR sqlserver.username = N'shared_sa'
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\DoesNotExist\XEvents\Track_SharedAdmin_LoginUsage.xel',
        max_file_size = 100,
        max_rollover_files = 20
)
WITH
(
    STARTUP_STATE = ON,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
);
GO

ALTER EVENT SESSION [Track_SharedAdmin_LoginUsage] ON SERVER STATE = START;
GO

Nothing fancy. Just enough to answer the real question: who is using that shared admin login, from which machine, and from which tool?

The first problem

Of course, the first run did not work.

The error looked like this:

Msg 25602, Level 17, State 23, Line 61
The target, "....package0.event_file", encountered a configuration error during initialization.
Object cannot be added to the event session.

That error turned out not to be about the login event at all. It was the event_file target.

The usual suspects:

• the folder does not exist
• the SQL Server service account cannot write there
• the path is wrong for the platform
• the filename is not specified correctly

On a local SQL Server instance, a normal local path is fine. On Azure SQL Managed Instance, that same approach is wrong, because event_file goes to Azure Storage instead of local disk.

In my case it was local, so the fix was old school: create the folder and make sure the SQL Server service account had write rights.

The sanity check that helps

Before trying to fix everything at once, I switched to a ring_buffer target to prove the session itself was valid.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'XE_Ring_Test')
    DROP EVENT SESSION [XE_Ring_Test] ON SERVER;
GO

CREATE EVENT SESSION [XE_Ring_Test]
ON SERVER
ADD EVENT sqlserver.login
ADD TARGET package0.ring_buffer;
GO

ALTER EVENT SESSION [XE_Ring_Test] ON SERVER STATE = START;
GO

If ring_buffer works and event_file does not, your problem is almost certainly target path or permissions.

That small test saves a lot of time.

Reading the data back

Once the session was running, there were two easy ways to inspect the output.

The first one is SSMS. It can open .xel files directly, and it can merge multiple files, filter, aggregate, and export the data.

The second one is straight T-SQL:

SELECT *
FROM sys.fn_xe_file_target_read_file
(
    'C:\Temp\XEvents\Track_SharedAdmin_LoginUsage*.xel',
    NULL,
    NULL,
    NULL
);

A more useful parsing example looked like this:

;WITH Raw AS
(
    SELECT
        timestamp_utc,
        object_name,
        file_name,
        file_offset,
        TRY_CAST(event_data AS xml) AS x
    FROM sys.fn_xe_file_target_read_file
    (
        N'C:\Temp\XEvents\Track_SharedAdmin_LoginUsage*.xel',
        NULL,
        NULL,
        NULL
    )
    WHERE object_name = N'login'
),
Parsed AS
(
    SELECT
        timestamp_utc AS event_time_utc,
        x.value('(/event/action[@name="server_principal_name"]/value)[1]', 'sysname') 
              AS server_principal_name,
        x.value('(/event/action[@name="username"]/value)[1]', 'sysname')              
              AS username,
        x.value('(/event/action[@name="nt_username"]/value)[1]', 'nvarchar(256)')     
              AS nt_username,
        x.value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(256)') 
              AS client_hostname,
        x.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') 
              AS client_app_name,
        x.value('(/event/action[@name="database_name"]/value)[1]', 'sysname')         
              AS databasename
    FROM Raw
    WHERE x IS NOT NULL
)
SELECT
    server_principal_name,
    username,
    nt_username,
    client_hostname,
    client_app_name,
    databasename,
    COUNT(*) AS events_seen,
    MIN(event_time_utc) AS first_seen_utc,
    MAX(event_time_utc) AS last_seen_utc
FROM Parsed
GROUP BY
    server_principal_name,
    username,
    nt_username,
    client_hostname,
    client_app_name,
    databasename
ORDER BY events_seen DESC, last_seen_utc DESC;

That was enough to build a pretty decent picture of who was still using the shared login, from where and what databases are they using.

What this project was really about

The XE session was never the end goal.

The end goal was to replace one shared overpowered login with personal, named access. That makes auditing easier, ownership clearer, and bad habits much harder to hide behind.

If somebody genuinely needs sysadmin, give that right to a named admin account. Preferably a Windows backed identity or controlled group, not another anonymous shared SQL login with a password everybody knows.

FAQ (my personal XE newby questions)

How fast are this file growing?

At one point I just wanted to know if the files were growing and how fast.

This worked nicely:

Get-ChildItem "C:\Temp\XEvents\Track_SharedAdmin_LoginUsage*.xel" |
Select-Object Name, LastWriteTime, @{Name='SizeMB';`
Expression = { [math]::Round($_.Length / 1MB, 2) }}

And for total size:

[math]::Round(((Get-ChildItem "C:\Temp\XEvents\Track_SharedAdmin_LoginUsage*.xel"
| Measure-Object Length -Sum).Sum / 1MB), 2)

Note: These are not local file on your computer, the files exist on the SQL Server!

The obvious next question: how big do these files get?

That depends on MAX_FILE_SIZE and MAX_ROLLOVER_FILES.

If you configure:

max_file_size = 100,
max_rollover_files = 20

then each file grows to about 100 MB before rollover, and older rollover files are retained according to that limit.

One important gotcha: if you do not set a sensible rollover policy, analysis gets messy long before storage becomes the real issue.

Will this hurt my production performance?

“It gets slower when there are more files” That was the next thing I noticed.

The good news: that slowdown is often more about reading and viewing the data than about the live collection itself.

The less good news: when you point SSMS or sys.fn_xe_file_target_read_file() at a big wildcard over many .xel files, you are asking it to open and parse a lot of data.

So the short version is:

• many .xel files mostly hurt analysis and viewing
• a narrow live XE session is usually still fine
• a giant wildcard read across all historical files is where things get slower

Does stopping the session delete the files?

Nope.

Stopping the session stops new writes. Dropping the session removes the session definition. Neither action automatically deletes the existing .xel files from disk.

Those are just files, so if you want them gone, you delete them separately.