Auditing Who Holds CONTROL SERVER and IMPERSONATE Rights

The sysadmin list looks short, but another login can still control the instance. CONTROL SERVER and impersonation permissions deserve their own audit, including grants inherited through server roles. A specific impersonation grant is as powerful as its target, so read the target as carefully as the grantee.

Separate CONTROL SERVER From Impersonation

CONTROL SERVER gives extensive server authority. It is similar to sysadmin, but the mechanisms are not identical: sysadmin bypasses permission checks, while a principal with CONTROL SERVER can be subject to specific denials. Treat broad control as a serious administrative exposure. Do not assume a login without the sysadmin badge is low privilege.

IMPERSONATE ANY LOGIN enables changing execution context to other logins. IMPERSONATE on one login is narrower. Its risk depends on that login's rights and accessible paths. I inspect both grants and their targets. Which powerful identity can this account become? That question is more useful than counting permission rows.

List Direct CONTROL SERVER Grants and Denials

Server permission rows identify grantee, grantor, state, permission, and target class. Include DENY rows in the evidence rather than filtering them away. Fixed server-role permissions do not all appear in this catalog, so this query must be paired with membership review. Run with adequate metadata visibility, preferably through a controlled audit identity.

SELECT grantee.name AS grantee_name,grantee.type_desc,
       p.state_desc,p.permission_name,p.class_desc,
       target.name AS impersonated_login,grantor.name AS grantor_name
FROM sys.server_permissions AS p
JOIN sys.server_principals AS grantee
  ON grantee.principal_id=p.grantee_principal_id
JOIN sys.server_principals AS grantor
  ON grantor.principal_id=p.grantor_principal_id
LEFT JOIN sys.server_principals AS target
  ON p.class=101 AND target.principal_id=p.major_id
WHERE p.permission_name IN
(N'CONTROL SERVER',N'IMPERSONATE ANY LOGIN',N'IMPERSONATE')
   OR (p.permission_name=N'CONTROL' AND p.class=101)
ORDER BY grantee.name,p.permission_name,target.name;

A grant to a user-defined server role reaches its members. A grant with grant option also permits onward delegation. Save the grantor so removal planning can examine those dependencies. I keep direct grants separate from inherited paths in the report; otherwise the same login can look like several unrelated exposures.

Trace CONTROL SERVER Through Nested Server Roles

User-defined server roles can contain other user-defined server roles. Walk the membership graph, carrying the original member and each reachable role. The query then shows sysadmin paths and selected permissions on those roles. A cycle guard and recursion limit make a malformed or unexpected graph visible instead of leaving the collector running indefinitely.

WITH paths AS
(
    SELECT m.member_principal_id AS member_id,m.role_principal_id AS role_id,
           CAST(N'/'+CONVERT(nvarchar(12),m.member_principal_id)+N'/'
                +CONVERT(nvarchar(12),m.role_principal_id)+N'/' AS nvarchar(max)) AS trail,
           CAST(r.name AS nvarchar(max)) AS role_path
    FROM sys.server_role_members AS m
    JOIN sys.server_principals AS r ON r.principal_id=m.role_principal_id
    UNION ALL
    SELECT p.member_id,m.role_principal_id,
           p.trail+CONVERT(nvarchar(12),m.role_principal_id)+N'/',
           p.role_path+N' -> '+r.name
    FROM paths AS p
    JOIN sys.server_role_members AS m ON m.member_principal_id=p.role_id
    JOIN sys.server_principals AS r ON r.principal_id=m.role_principal_id
    WHERE p.trail NOT LIKE N'%/'+CONVERT(nvarchar(12),m.role_principal_id)+N'/%'
)
SELECT member.name AS login_or_group,p.role_path,r.name AS granting_role,
       CASE WHEN r.name=N'sysadmin' THEN N'SYSADMIN MEMBERSHIP'
            ELSE sp.permission_name END AS authority,
       sp.state_desc,target.name AS impersonated_login
FROM paths AS p
JOIN sys.server_principals AS member ON member.principal_id=p.member_id
JOIN sys.server_principals AS r ON r.principal_id=p.role_id
LEFT JOIN sys.server_permissions AS sp
  ON sp.grantee_principal_id=p.role_id
 AND (sp.permission_name IN(N'CONTROL SERVER',N'IMPERSONATE ANY LOGIN',N'IMPERSONATE')
      OR (sp.permission_name=N'CONTROL' AND sp.class=101))
LEFT JOIN sys.server_principals AS target
  ON sp.class=101 AND target.principal_id=sp.major_id
WHERE member.type IN('S','U','G','E','X')
  AND (r.name=N'sysadmin' OR sp.permission_name IS NOT NULL)
OPTION(MAXRECURSION 100);

Include Windows and Other Authority Paths

Every login implicitly belongs to public, so membership rows alone do not expand that role. Inspect its grants explicitly. CONTROL on a particular login also implies impersonation of that login, so the selected permission queries include it. The following query identifies the public-role path for visible login principals.

SELECT l.name AS login_or_group,p.permission_name,p.state_desc,
       target.name AS impersonated_login
FROM sys.server_principals AS l
CROSS JOIN sys.server_principals AS r
JOIN sys.server_permissions AS p ON p.grantee_principal_id=r.principal_id
LEFT JOIN sys.server_principals AS target
  ON p.class=101 AND target.principal_id=p.major_id
WHERE r.name=N'public' AND r.type='R'
  AND l.type IN('S','U','G','E','X')
  AND (p.permission_name IN
       (N'CONTROL SERVER',N'IMPERSONATE ANY LOGIN',N'IMPERSONATE')
       OR (p.permission_name=N'CONTROL' AND p.class=101));

The catalog lists a Windows group principal, not every directory member inside it. Review group membership with the directory owner, including nested groups. A real login token can differ from what a simple EXECUTE AS test reconstructs. Fixed roles, role ownership, module signing, and other permission paths also deserve review beyond these three grants.

Use this audit as a focused inventory, not a claim to have discovered every escalation route. I compare the results with service identities and emergency-access accounts. An old support group can survive long after the reason for its privilege has expired. The permission does not retire itself out of politeness.

Test One Candidate in a Controlled Session

For a known SQL login, test its effective server permissions with fn_my_permissions under EXECUTE AS. Use an existing audit candidate and a dedicated session. Capture the result, revert, and verify your original context. This does not send a real client through its authentication path, so keep that distinction in the evidence.

SELECT SUSER_SNAME() AS original_context;
EXECUTE AS LOGIN=N'ExistingLoginToAudit';
BEGIN TRY
    SELECT SUSER_SNAME() AS tested_context;
    SELECT * FROM sys.fn_my_permissions(NULL,N'SERVER')
    WHERE permission_name IN
      (N'CONTROL SERVER',N'IMPERSONATE ANY LOGIN');
    SELECT IS_SRVROLEMEMBER(N'sysadmin') AS is_sysadmin,
           HAS_PERMS_BY_NAME(N'ExistingTargetLogin',N'LOGIN',N'IMPERSONATE')
               AS can_impersonate_target;
    REVERT;
END TRY
BEGIN CATCH
    REVERT;
    THROW;
END CATCH;
SELECT SUSER_SNAME() AS restored_context;

Replace both names with reviewed existing logins. A login without these rights returns no permission rows and two zeros. Do not create a privileged account just to make this demonstration convenient. Your audit operator must itself have the required impersonation authority. A denied test is an audit limitation to resolve, not a reason to grant the candidate extra rights.

Remove the Actual Source of Access

Determine whether the right comes from a direct grant, a role, or a directory group. Revoking a direct grant does not remove inherited permission. Replacing broad rights with narrower duties also needs application and job tests. Keep a separate validated administrative session before changing an account used for operations.

USE master;
REVOKE CONTROL SERVER FROM [ReviewedLogin];
REVOKE IMPERSONATE ANY LOGIN FROM [ReviewedLogin];
REVOKE IMPERSONATE ON LOGIN::[ReviewedTargetLogin] FROM [ReviewedLogin];

These examples apply to reviewed principals. They require a separate decision for each actual grant. Execute only the statement matching an actual GRANT or GRANT_WITH_GRANT_OPTION state. Do not revoke a DENY as a cleanup shortcut. Removing a denial can increase access, which is the opposite of the intended change. If grant-option dependencies exist, investigate them before considering CASCADE. A convenient revoke can remove access from downstream operators who were never part of the original request.

Verify the Required Work Still Runs

A DENY row deserves context. It can affect a non-sysadmin principal while sysadmin still bypasses the check. Do not subtract grants and denials mechanically to claim an effective result. Use the actual permission hierarchy and a controlled token test. If a login can impersonate a privileged target, test the target's authority separately in the lab. Record the two steps rather than describing every impersonation grant as unrestricted administration.

Review membership changes as well as permission changes. A login can regain a right through another role after a direct revoke. Save the nested paths before and after the change. A directory group requires a directory review too; the SQL membership rows cannot show its full population.

Keep an audit owner and review date for exceptions. Emergency access can be necessary, but it should have a named purpose and a tested recovery process. Verify that removal will not lock out the only available administrator. Preserve a separate authorized session during the change and prove normal administrative access afterward. I check that return path before narrowing the account. Access removal should be deliberate, observable, and recoverable.

Repeat the catalog and effective-permission checks after removal. Then test the jobs, deployments, or support operations the account still needs. Record what was removed, why, who owns the identity, and the replacement rights. An account with no unexplained privilege is easier to maintain than one whose required tasks now fail quietly.

I favor a small, evidenced change over a broad permission purge. The audit should expose the path to power and the business reason, then verify the narrowed path. Keep privileged membership, grants, and impersonation targets in the same review cycle.

Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and List Users with System Admin (sysadmin) Rights.

A short sysadmin list is not a full privilege audit, it is one part of tracing effective server authority.

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