SQL Server Audit / Extended Events – Role‑based (sysadmin) filtering limitations

Question

SQL Server Audit / Extended Events – Role‑based (sysadmin) filtering limitations

Uday Paleti 5

Hello Microsoft Team,

We are implementing security monitoring for sysadmin account usage on an on‑premises SQL Server instance and would like to know what EX events or Audit to achieve our goal?

We tried Audit and XE events but those are capturing all logins (Sysadmin,Non Sysadmin,Developer accounts etc.) so we do not want noise on Prod server. We want capture who is login with sysadmin rights other than DBA.

Please help me possible solution. Thanks.

Our requirement

We want to:

Capture login activity related to sysadmin accounts
Identify approved vs unapproved sysadmin usage
Exclude specific trusted DBA/service accounts
No performance impact on Prod servers.**

Below event not creating and getting error
**CREATE EVENT SESSION Audit_Sysadmin_Logins_test

ON SERVER

ADD EVENT sqlserver.login

(

ACTION (

    sqlserver.client_app_name,

	sqlserver.username,  

    sqlserver.client_hostname,

    sqlserver.server_principal_name,

    sqlserver.session_id

)

WHERE

(

    -- Only successful logins

    [result] = 0

    -- Must be sysadmin

    AND IS_SRVROLEMEMBER('sysadmin', [server_principal_name]) = 1

    -- Exclude DBA/service accounts

    AND [server_principal_name] NOT IN (

        'DOMAIN\dba_user1',

        'DOMAIN\dba_user2'

       

    )

)

)

ADD TARGET package0.event_file

(

SET 

filename = N'R:\Extended_Events\Audit_Sysadmin_Logins.xel',

    max_file_size = 50,

    max_rollover_files = 5

);

GO

Uday Paleti 5 Reputation points

2026-04-24T17:34:15.38+00:00

I tried chatgpt & Copilot and I didn't get the solution what I want. So please provide XE events to capture only " SYSADMIN" users other than DBA accounts.
Anonymous

2026-04-29T05:30:09.4766667+00:00
Hi @Uday Paleti

Thanks for the detailed requirement. Let me address this clearly.

Why Your XE Session Failed

The error occurs because IS_SRVROLEMEMBER() is a T-SQL scalar function and is not supported inside XE predicate WHERE clauses. The XE filter engine only accepts:

Event fields (e.g., [result], [server_principal_name])

Built-in XE comparator operators (=, <>, <, >)

This is a hard engine limitation not a syntax or permissions issue. No amount of rewriting will make IS_SRVROLEMEMBER() work inside an XE predicate. This is why ChatGPT and Copilot couldn't give you a working solution either.

What Is Actually Possible

There is no pure XE-only solution for role-based filtering at capture time. However, a two-part approach fully covers all your requirements:

Part 1 Fix Your XE Session

Remove IS_SRVROLEMEMBER() from the WHERE clause completely

Replace it with direct account exclusions using supported <> comparisons on [server_principal_name]

Keep [result] = 0 for successful logins only

This fixes the error and the session will create and run cleanly

Part 2 Logon Trigger for Real-Time Sysadmin Detection

A Logon Trigger (ON ALL SERVER FOR LOGON) is the only native SQL Server mechanism that can call IS_SRVROLEMEMBER() at actual login time

It fires for every login, checks role membership, and logs only sysadmin accounts into a dedicated audit table

Inside the trigger you can maintain an approved DBA allowlist and stamp each record as IsApprovedDBA = 1 or 0 automatically

This gives you the approved vs unapproved classification your requirement asks for Thanks for the detailed requirement. Let me address this clearly.

One important note:

As the super user correctly pointed out, if a malicious user is temporarily elevated to sysadmin on the fly, a static account list alone won't catch that. The Logon Trigger approach does cover this scenario because it checks actual role membership dynamically at every login, not against a hardcoded list.

Thanks,
Akhil.
Uday Paleti 5 Reputation points

2026-04-29T13:06:36.3866667+00:00

Hi @Akhil Gajavelly Thanks for your input. I’ll try this method and see if it gives me the result I want.
Anonymous

2026-05-07T09:25:15.0533333+00:00

Hi @Uday Paleti ,

Just checking in were you able to try the suggested approach?
Please let us know if it worked for you or if you’re still facing any issues. Happy to assist further.

Thanks,
Akhil.
Anonymous

2026-05-12T08:06:20.21+00:00

Hi @uday paleti ,

Thanks for the update, and glad to hear the modified XE session is working on your test servers.

Your current approach is the most practical lightweight solution since XE does not support dynamic role checks like IS_SRVROLEMEMBER() inside predicates.

Monitoring it in test before Production rollout is a good approach. Thanks as well for sharing the working script it will help others facing the same limitation.

Thanks,
Akhil.

Answer recommended by moderator

Uday Paleti 5

Thanks, @Anonymous for the follow-up.
Due to time constraints, we created XE events for specific accounts on each server. However, I tried your suggestion (mentioned in Part 1) by modifying the script, and it works fine on the test servers. I will monitor these events for a few weeks, and if they provide the required output, we will implement them on the production servers as well. Thanks again for your suggestions.

CREATE EVENT SESSION Audit_Sysadmin_Logins_test

ON SERVER

ADD EVENT sqlserver.login

(

ACTION

(

    sqlserver.client_app_name,

    sqlserver.username,

    sqlserver.client_hostname,

    sqlserver.server_principal_name,

    sqlserver.session_id

)

WHERE

(

    -- Exclude DBA / service accounts

    

     [server_principal_name] <> 'DOMAIN\dba_user1'

    AND [server_principal_name] <> 'dba_user2'

	AND [server_principal_name] <> 'DOMAIN\dba_use3'

	AND [server_principal_name] <> 'dba_user4'

)

)

ADD TARGET package0.event_file

(

SET

    filename = N'R:\Extended_Events\Audit_Sysadmin_Logins.xel',

    max_file_size = 50,

    max_rollover_files = 5

);

GO

0 comments

1 additional answer

Your answer

Uday Paleti 5 Reputation points

2026-04-24T17:34:15.38+00:00

I tried chatgpt & Copilot and I didn't get the solution what I want. So please provide XE events to capture only " SYSADMIN" users other than DBA accounts.
Uday Paleti 5 Reputation points

2026-04-29T13:06:36.3866667+00:00

Hi @Akhil Gajavelly Thanks for your input. I’ll try this method and see if it gives me the result I want.
Anonymous

2026-05-07T09:25:15.0533333+00:00

Hi @Uday Paleti ,

Just checking in were you able to try the suggested approach?
Please let us know if it worked for you or if you’re still facing any issues. Happy to assist further.

Thanks,
Akhil.
Anonymous

2026-05-12T08:06:20.21+00:00

Hi @uday paleti ,

Thanks for the update, and glad to hear the modified XE session is working on your test servers.

Your current approach is the most practical lightweight solution since XE does not support dynamic role checks like IS_SRVROLEMEMBER() inside predicates.

Monitoring it in test before Production rollout is a good approach. Thanks as well for sharing the working script it will help others facing the same limitation.

Thanks,
Akhil.

Answer 1

Erland Sommarskog 137.6K MVP Volunteer Moderator

I'm afraid that there is no straightforward way to do what you want to do. I think the best you can do is to set up an event session where you explicitly list the members of sysadmin (with the exception of your two DBA users). If you add new persons to sysadmin, you will to modify the events session accordingly.

I should point out that there is quite a flaw here: A malicious user may have found a way to elevate to sysadmin on the fly when the user wants to perform something evil. It goes without saying that this is something you want to capture. Although, exactly to do that, hm, I am not sure.

Uday Paleti 5 Reputation points

2026-04-24T21:30:24.2233333+00:00

Thanks, for your reply. I tried to create event for explicit users around 200 but SQL Server didn’t allow to create because it exceeded the 3000-character limit. So I am looking for a good solution to capture the sysadmin accounts.
Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2026-04-24T21:33:41.2733333+00:00

You have 200 sysadmin users on the server? Cough, that is not good.

How many users do you have in total? If they are that many, I'm inclined to think that the best is have a session that filters out the known DBAs, then you need to filter data when you analyse it.
Uday Paleti 5 Reputation points

2026-04-24T21:44:52.93+00:00

Sorry for the confusion. The 200 users are not on a single system; they were identified across all systems.

We observed that around 7 accounts per system are logging in with sysadmin privileges. This number varies by system, but the overall total is approximately 200 accounts.

We are looking for a single script that includes all these logins and can be deployed across all servers, so that we can track them whether they attempt to log in to one system or multiple systems. Also we want monitor for 1 month and so before making final decision if we can delete or keep the account.
Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2026-04-24T21:50:24.1233333+00:00

You would create an event session all servers with only a dummy filter. Then you deploy a job on all servers that daily (or several times a time) alters the session by adding a filter for the sysadmin users, filtering out the usual suspects (that is, your DBA team and the built-in users). This way you capture if anyone is added to sysadmin on the server.
Uday Paleti 5 Reputation points

2026-04-24T21:55:10.3833333+00:00

Thanks, Erland, for your prompt response. Can you please share the script or link so that I can run on my test environment?
Uday Paleti 5 Reputation points

2026-04-24T21:56:24.6+00:00

Thanks, for your prompt response. Can you please share the script or link so that I can run on my test environment?
Erland Sommarskog 137.6K Reputation points MVP Volunteer Moderator

2026-04-24T21:59:19.5366667+00:00

Sorry, I have nothing canned. I'm just outlining the solution, but I leave the hard work for you. But it should not be too tricky. It's an exercise in dynamic SQL. If you want tips on using dynamic SQL, I have an article about that on my web site: https://www.sommarskog.se/dynamic_sql.htmlBut you don't have to build the SQL strings in T-SQL. If you are more comfortable with Powershell or what ever, you can do the string work in that language. In the end, you would generate ALTER EVENT SESSION statements, so that would be T-SQL.
Uday Paleti 5 Reputation points

2026-04-24T22:07:05.5366667+00:00

Sure, thanks a lot for your response. I will go through your website and analyze it further.