The sysadmin list is usually shorter than the risk it represents. Auditing who has sysadmin means checking direct members, group paths, and old logins that nobody remembers using.
Start With Direct Membership to See Who Has sysadmin
The fixed server role sysadmin bypasses ordinary permission checks. A member can read and change data across the instance, alter security, and affect availability. Treat the list of who has sysadmin as a control that needs a named owner and periodic review. Do not assume a login is harmless because it has not appeared in a recent application inventory.
I start with the role membership query rather than a screenshot from an administration tool. It is reproducible and easy to compare later. Capture principal type and disabled state. A disabled login remains useful history, but a current enabled member needs a reason for its access. Who will answer for each member on your server?
SELECT member_principal.name AS member_name,
member_principal.type_desc AS member_type,
member_principal.is_disabled,
role_principal.name AS role_name
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS role_principal
ON rm.role_principal_id = role_principal.principal_id
JOIN sys.server_principals AS member_principal
ON rm.member_principal_id = member_principal.principal_id
WHERE role_principal.name = N'sysadmin'
ORDER BY member_principal.name;Remember Windows Groups
A Windows group in the sysadmin role can grant access to several people without each person appearing as a SQL Server login. Nested directory groups can extend that path further. SQL Server’s role query shows the server principal, not a complete live directory membership tree. Ask the directory team for the current direct and nested membership, with the time of the export.
I have seen a carefully reviewed SQL list hide a broad group behind one innocent-looking name. The group name was technically present all along. Review who can change that group as well as who belongs to it. A group administrator who can add members effectively controls a path to sysadmin. That is part of the access model, even though it sits outside the database engine.
Check Effective Membership for Who Has sysadmin
IS_SRVROLEMEMBER can answer whether a given login resolves as a member in the current environment. It is a useful spot check, especially when Windows group access is involved. It does not replace a directory inventory, and resolution can depend on the current security context. Use it for a named principal and record the result with the date of the test.
The query below demonstrates the call. Replace the sample Windows principal with one that exists in your domain. A null answer needs investigation rather than being treated as no. Keep the direct membership query as the authoritative instance inventory.
SELECT IS_SRVROLEMEMBER
(N'sysadmin', N'DOMAIN\ExampleUser') AS is_sysadmin;Find Forgotten Logins
List server principals with create and modify dates. Old dates do not prove neglect, but they help frame the review. Compare the list with service inventories, job owners, linked-server mappings, and connection evidence. A login that has not connected recently can still be required by a quarterly process. Silence in a short observation window is not permission to delete it.
I ask the owner to identify the application or operational task before revoking access. If nobody can, I plan a controlled disable with monitoring and a rollback path. This is safer than immediate removal, which can leave a job or emergency procedure broken. The goal is least privilege with continuity, not a clean-looking list for its own sake.
SELECT name,
type_desc,
is_disabled,
create_date,
modify_date
FROM sys.server_principals
WHERE type IN (N'S', N'U', N'G')
ORDER BY type_desc, name;
Examine the Built-In Account
The sa login needs a separate review. Check whether it is enabled, how its credential is controlled, and whether anything still depends on it. Renaming or disabling it can reduce a predictable attack target, but an untested change can break legacy automation. Do not treat the visible name alone as a complete security measure. Strong authentication, restricted exposure, and monitoring remain necessary.
A shared administrator login makes accountability difficult. Named access through an approved group or individual principal gives better review evidence. Keep a documented emergency path, but protect it as carefully as any other high privilege account. An emergency credential that everybody knows is not a plan. It is a social event waiting to happen.
Review Job and Service Dependencies
A SQL Agent job can run under a login or rely on a proxy. A service account or automation identity can need specific rights without needing sysadmin. Trace each privileged login to its actual task. For jobs, review owner names and steps before changing membership. For applications, test the exact operation with narrower permissions in a nonproduction environment.
I prefer to remove broad access only after the replacement permission has been demonstrated. Permission changes fail in surprising places when a nightly branch of code never ran in the test. Document the expected failure signals and rollback. A controlled reduction beats a heroic midnight fix. The audit should produce work items that can be verified, not just names to cross out.
Record Approval and Change
For every remaining member, record the principal, access path, owner, business reason, and next review date. Separate direct SQL members from Windows groups and nested memberships. Keep a snapshot so the next review can focus on changes. If group membership is managed outside SQL Server, establish how the directory team will provide an auditable export.
Changes to sysadmin deserve a reviewed script and an explicit rollback path. Confirm the new access route before removing the old one. Test with a login that has the intended role, not only from an already privileged session. Otherwise a successful test proves little. The access review is complete when the people who own the work accept the remaining paths.
Make the Review of Who Has sysadmin Repeatable
Schedule the instance query and directory membership check together to track who has sysadmin. Alert on new direct members, newly enabled principals, and unexpected group changes. Distinguish approved emergency access from permanent membership. A report that repeats every unchanged member can still be useful, but put changes at the top where a reviewer will see them.
I like a quarterly human review even when automated checks run daily. Automation catches differences. People decide whether a role is still justified. The question is simple: if this login were compromised today, would its sysadmin rights be necessary for the task it performs? If the answer is no, plan the narrower path.
Related reading on this blog: Creating System Admin (SA) Login With Empty Password: Bad Practice and Understanding Grant, Deny, and Revoke Permissions.
A sysadmin list is not a security review, it is the start of tracing every access path.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
