Permissions in detail

This builds on Database Permissions, which covers the setup script and the three tiers of access. This page goes through what each grant actually unlocks, for Advisor and for Compare, and what you see on screen when one is missing.

VIEW SERVER STATE

Backs every Advisor check built on a dynamic management view: memory clerks, system memory, system information, wait statistics, sessions, threads, transaction locks, waiting tasks, and the missing index DMVs. Without it, those checks report an error rather than a result. Compare's own error handling names this permission specifically when a query it runs is refused.

VIEW ANY DEFINITION

Backs Compare's server-level security comparisons: server audits and audit specifications, server principals, permissions and role membership, credentials, cryptographic providers, linked servers and their remote logins and security, endpoints, backup devices, and server triggers.

This one is worth watching closely, because it fails quietly. Without it, these queries still run, but they return only the rows the login can already see. That can look like a clean comparison when it is really an incomplete one.

CONNECT ANY DATABASE

Advisor and Compare both walk every database in scope, switching into each one on the same connection to run per-database checks and comparisons. This grant, or a mapped user in every database, is what lets that switch succeed.

This is the one exception to how the rest of Studio degrades. Everywhere else, a missing permission fails one check or one comparison and the run carries on. A database Studio cannot switch into stops the whole Advisor run, not just the checks for that database.

msdb access

SELECT on msdb covers backupset (backup history, and the per-database last backup date), suspect_pages (corruption), sysjobs (Agent job owners) and sysdbmaintplans (maintenance plan comparison), plus the Agent comparison tables sysjobsteps, sysschedules, sysjobschedules, sysoperators, sysalerts, sysnotifications and sysproxies.

securityadmin, for xp_readerrorlog

xp_readerrorlog backs Advisor's failed logins and slow I/O checks, and it needs more than an EXEC grant: the login also has to be a member of securityadmin (or sysadmin). Without both, the checks that depend on it error rather than run.

SQLAgentReaderRole

Compare's Agent job and schedule comparisons read sysjobs and sysschedules through msdb. A non-sysadmin login without further membership sees only the jobs and schedules it owns, which understates the comparison. Membership of msdb's SQLAgentReaderRole gives it visibility of every job and schedule on the instance, without needing sysadmin.

SQLAgentReaderRole does not extend to Agent operators, alerts, proxies or notifications; there is no reader role for those.

What needs sysadmin

A small, fixed set of Compare's checks have no permission narrower than sysadmin:

  • SQL Server Agent service state, which calls master.dbo.xp_servicecontrol.
  • Database Mail configuration, accounts and profiles, which call msdb.dbo.sysmail_help_configure_sp, sysmail_help_account_sp and sysmail_help_profile_sp. msdb's DatabaseMailUserRole covers sending mail, not reading how it is configured.
  • Agent operators, alerts, proxies and operator notifications, which SQLAgentReaderRole does not cover.

Applying any fix script Advisor generates also needs whatever that script calls for. That is the operator's action when they choose to run it, not something the Aireforge login does on its own.

Azure SQL Database

The Azure comparisons read sys.firewall_rules, sys.geo_replication_links and sys.resource_usage. These need the dbmanager or loginmanager database role, or the equivalent server administrator account; the server roles used elsewhere on this page do not apply to Azure SQL Database.

What you see when a permission is missing

Advisor shows a failing check as an error item reading "There was an error running this check" and moves on to the next check. There is a setting to abort the whole run on the first error instead; it is off by default.

The exception is a database Advisor cannot switch into. Rather than failing that one database's checks, it cancels the rest of the run. This is the practical effect of missing CONNECT ANY DATABASE, and it is why that grant matters more than the others.

Compare reports "Error executing query" against the affected comparison and continues with the next one.

Neither module silently returns an empty result because of a missing permission. The one exception is VIEW ANY DEFINITION: there, the query succeeds and returns fewer rows, which can pass for a clean result unless you know to look for it.