master Server Permissions

Overview

The Logins report lists who exists and which roles they are in. This page answers the question underneath that one, which a role list cannot: what has been granted directly.

  • A login in no role at all can hold CONTROL SERVER.
  • A login that looks harmless can hold IMPERSONATE ANY LOGIN, which is sysadmin by a longer route.

Those grants are invisible on every other page in this product.

The master Server Permissions page: server level rights, the ones that are sysadmin under another name first
The whole page. Seventy explicit grants and thirteen logins in sysadmin, the bars ranking principals by what they hold, and the grid naming every right with the clause that says why it matters.

Where to find it

Expand a server in the tree, expand the master database, then Master → Server Permissions.

Three things this does that a plain listing does not

It puts the dangerous dozen at the top

The permissions that are sysadmin in all but name, each with a clause saying why it is, instead of sorting forty permissions alphabetically and leaving the reader to know which ones matter.

CONTROL SERVER, IMPERSONATE ANY LOGIN, ALTER ANY LOGIN, ALTER ANY SERVER ROLE, ALTER ANY CREDENTIAL, ALTER ANY LINKED SERVER, ADMINISTER BULK OPERATIONS, UNSAFE ASSEMBLY, EXTERNAL ACCESS ASSEMBLY, ALTER SETTINGS, ALTER TRACE and AUTHENTICATE SERVER are the ones that carry a clause.

It shows DENY as well as GRANT

A page that filters to grants describes rights that nobody actually has, because a DENY anywhere beats a GRANT everywhere.

It walks role membership rather than joining it once

Since SQL Server 2012 a user defined server role can contain another server role, so a login two levels down from one holding CONTROL SERVER holds it too, and a single join does not find that.

Reading the chart

The bars: one per principal, the ones that are sysadmin under another name first
The line under the header is the finding: one grant is sysadmin under another name, held by somebody who is not in the role, and one DENY is in place.

One bar per principal, ranked with the ones holding sysadmin by a longer route at the top, and the count of rights on the right.

The bars draw the top thirteen of the forty seven principals. The grid holds them all, which is where a login with a single ordinary grant appears.

Reading the grid

The grid: principal, right, state, securable and why it matters
The dangerous dozen sort to the top. The first row is a CONTROL SERVER grant, and the Why it matters column says in one clause what that is.
Column What it is
Principal The login or server role that holds it.
Kind SQL login, Windows login, Windows group, or server role.
Right The permission.
State GRANT, GRANT WITH GRANT OPTION, or DENY.
On The securable: the server, an endpoint, a login, an availability group.
Granted by The grantor.
Why it matters The clause, for the permissions that are sysadmin in all but name. Blank for the ordinary ones.

A row reached through nested roles says which role chain carried it.

The toolbar

Control What it does
Logins Opens the Logins report.
Security posture Opens the Security Posture report.
Credentials Opens master Credentials.
Endpoints Opens master Endpoints, whose CONNECT grants are class 105 rows here.

Where the data comes from

Source What it gives
sys.server_permissions The grants and denies, with class and securable.
sys.server_principals The principals, their type and their enabled state.
sys.server_role_members Membership, walked rather than joined once.
sys.endpoints The names behind class 105 securables.
sys.availability_groups The names behind availability group securables.
Report Why you would go there
Logins Who exists, and which fixed roles they are in.
Security Posture The instance level summary.
master Endpoints The CONNECT grants, in endpoint terms.
master Credentials What ALTER ANY CREDENTIAL would reach.
master Change Ledger When a grant was made, from the default trace.

Frequently asked questions

A login is in no role and can do everything. Look for CONTROL SERVER or IMPERSONATE ANY LOGIN on this page. Both are sysadmin in practice and neither appears on any role list.

Why show DENY? Because a page that showed only grants would describe rights nobody actually has. A DENY anywhere in the chain beats every grant.

A login holds a permission through two roles. That is the nesting walk. Since SQL Server 2012 a server role can contain another, and the page says which chain carried the right.

Is a grant of ALTER TRACE dangerous? It carries a clause because it lets somebody read query text through a trace. Whether that matters depends on what the queries carry, which is why the page explains rather than scores.