SQL SERVER – Script to Audit Login and Role Member Change

A client once asked me who had added an account to the sysadmin role. My first question was whether they were auditing that. They were not. So the honest answer was that nobody would ever know. This is the script I gave them to audit login and role changes, so it could not happen twice, rebuilt and tested on SQL Server 2025. The part to take away is not the script. It is that this catches the attempts that fail.

Set It Up

Three statements. An audit, which says where to write. A specification, which says what to record. Both switched on.

USE master;
GO
CREATE SERVER AUDIT login_perm_audit
TO FILE (FILEPATH = 'C:\SQLAudit\');
GO
ALTER SERVER AUDIT login_perm_audit WITH (STATE = ON);
GO
CREATE SERVER AUDIT SPECIFICATION login_audit_spec
FOR SERVER AUDIT login_perm_audit
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);
GO

Those three groups cover the three ways somebody gains power. A new or changed login is the principal group. Being added to a server role is the role member group. A permission granted directly is the permission group. Miss one and there is a route in you cannot see.

One thing before you run it. The SQL Server service account must be able to write to that folder. That is not checked until you switch the audit on. If you get Msg 33222 here, that is the cause, and I have written it up separately.

Check it actually started rather than assuming:

SELECT name, status_desc, audit_file_path
FROM   sys.dm_server_audit_status;

Test It Properly

Do not trust an audit you have not tried to fool. I made a login, put it in sysadmin, granted it a permission, then removed everything again.

CREATE LOGIN IntruderLogin WITH PASSWORD = N'<a password>',
    DEFAULT_DATABASE = master, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
GO
ALTER SERVER ROLE sysadmin ADD MEMBER IntruderLogin;
GO
GRANT VIEW SERVER STATE TO IntruderLogin;
GO
ALTER SERVER ROLE sysadmin DROP MEMBER IntruderLogin;
DROP LOGIN IntruderLogin;
GO

Reading It Back Without the Riddles

The audit file is read with a function, not a table. Read it raw and the answers look like this: CR, APRL, G, DPRL, DR. Those are the real action codes. They are also the whole reason people give up on SQL Server Audit.

Two system views turn them into English. This is the query worth keeping:

SELECT  CONVERT(varchar(19),
            DATEADD(MINUTE, DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME()),
                    f.event_time), 121)  AS local_time,
        a.name                           AS action_taken,
        m.class_type_desc                AS on_what,
        f.server_principal_name          AS who,
        f.target_server_principal_name   AS to_whom,
        f.object_name                    AS object_name,
        f.succeeded,
        f.client_ip,
        f.application_name
FROM    sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT) AS f
LEFT JOIN sys.dm_audit_class_type_map AS m
       ON m.class_type = f.class_type
LEFT JOIN sys.dm_audit_actions AS a
       ON a.action_id = f.action_id
      AND a.class_desc = m.securable_class_desc
ORDER BY f.event_time;

And here is what my five statements actually produced:

local_time            action_taken  on_what       who          to_whom        object_name  succeeded
2026-09-22 22:10:30   CREATE        SQL LOGIN     CORP\dba                 IntruderLogin        1
2026-09-22 22:10:30   ADD MEMBER    SERVER ROLE   CORP\dba  IntruderLogin  sysadmin             1
2026-09-22 22:10:30   GRANT         SERVER        CORP\dba  IntruderLogin                       1
2026-09-22 22:10:30   DROP MEMBER   SERVER ROLE   CORP\dba  IntruderLogin  sysadmin             1
2026-09-22 22:10:30   DROP          SQL LOGIN     CORP\dba                 IntruderLogin        1

Five statements, five rows, in plain words. Note that dropping the login did not drop the history. That is the point of writing to a file the database cannot reach back into.

The Bit Everybody Misses

Look at the last column in that query. succeeded.

I connected as an ordinary login with no privileges at all and tried to promote itself:

ALTER SERVER ROLE sysadmin ADD MEMBER joe4;

Msg 15151, Level 16, State 1
Cannot alter the server role 'sysadmin', because it does not exist or you do
not have permission.

It failed, as it should. And the audit recorded it anyway:

local_time            action_taken  who    to_whom  object_name  succeeded  application_name
2026-09-22 22:11:16   ADD MEMBER    joe4   joe4     sysadmin             0  SQLCMD

succeeded = 0. Somebody tried to make themselves a sysadmin and could not. That row is worth more than all five successful ones above it. A change that works is usually a colleague doing their job. A change that fails is almost never an accident.

So the query to run every morning is not a list of everything. It is this:

...
WHERE f.succeeded = 0
   OR f.object_name IN ('sysadmin', 'securityadmin', 'serveradmin')
ORDER BY f.event_time DESC;

Two Columns Worth Their Weight

client_ip and application_name come free with every row. Nobody looks at them. Mine said local machine and SQLCMD, which is unremarkable, because it was me.

On a real server they are the first two things an investigation asks for. A permission change from an application server at three in the morning tells a different story from the same change on a named desktop at eleven. Put both in your report from the start. You cannot add them later to events that were never captured.

Times Are in UTC

This caught me out and it is worth a warning. The event_time column in the audit file is UTC, not your server’s local time. My server clock said 22:10. The file said 16:40.

That is why the query above wraps event_time in a DATEADD. Skip the conversion and you will see a time hours off, then decide that either the audit or your memory is broken. The audit is right.

What to Do Before You Need It

Put the audit files somewhere the people being audited cannot reach. A sysadmin can drop the audit. They cannot quietly edit a file they have no permission to open.

Size MAX_ROLLOVER_FILES for how long you want to keep history. Unlimited is a decision about your disk, whether or not you meant it that way.

Add sys.dm_server_audit_status to the checks you already run each morning. An audit only protects you while it runs, and nothing will tell you it stopped.

And test it the way I did here, by doing the thing you are trying to catch. An audit nobody has tested is a folder of files and a feeling.

The row worth reading is not the change that worked, it is the one that did not.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.