Database Permissions

Aireforge Studio only reads a server, it never writes to it. Advisor builds fix scripts (sp_configure with RECONFIGURE, ALTER DATABASE, DBCC SHRINKFILE, sp_update_job, sp_change_users_login, and so on) and shows them to you; it does not run them. Applying one is a separate, deliberate step for whoever runs the script, and that person needs whatever permission the fix itself calls for. The Aireforge login does not.

The permissions below are for the login that Advisor and Compare query the server with. Estate and Script need nothing beyond the ability to connect: they run with whatever access your own connection or script already has.

Three tiers of access

A low-privilege login

With a login that can connect, plus read access to the databases you care about, Studio can already read the catalogue views: sys.databases, sys.tables, sys.indexes, sys.columns, sys.configurations, sys.database_files, sys.filegroups, sys.check_constraints, sys.foreign_keys and sys.identity_columns, plus trace flag state through DBCC TRACESTATUS. That covers the configuration and schema checks, and the comparisons built on those views. Anything to do with logins or principals shows only the rows the login itself can see.

Add four grants and Studio covers most of what Advisor and Compare run:

  • VIEW SERVER STATE, for the checks built on dynamic management views: memory, wait statistics, sessions, locks and the missing index DMVs.
  • VIEW ANY DEFINITION, for server-level security metadata: audits, principals, permissions, role membership, credentials, linked servers, endpoints and server triggers.
  • CONNECT ANY DATABASE, so Advisor and Compare can switch into every database in scope rather than needing a mapped user in each one. This one matters more than it looks: a database Studio cannot switch into stops the whole Advisor run, not just the checks for that database.
  • SELECT on the msdb tables Studio reads: backup history, suspect pages, and the Agent job, schedule and maintenance plan tables.

Two checks, failed logins and slow I/O, read the error log through xp_readerrorlog, which also needs securityadmin membership. That role can grant server permissions and reset SQL logins, so decide whether those two checks are worth it before granting it.

See Permissions in detail for exactly what each grant unlocks, and for what you see when one is missing.

What needs sysadmin

A handful of Compare's checks genuinely need sysadmin, because nothing narrower covers them: SQL Server Agent service state, Database Mail configuration, and Agent operators, alerts, proxies and notifications. Applying a fix script Advisor generates also needs whatever that script itself calls for, which is the operator's decision, not Studio's.

Everything else in the recommended set runs without sysadmin.

Setting up the login

Run this on the target instance to create a dedicated login with the recommended grants. Replace CHANGEME with a real password before you run it.

USE [master]
GO

CREATE LOGIN [Aireforge_user]
WITH PASSWORD = N'CHANGEME',
     DEFAULT_DATABASE = [master],
     CHECK_EXPIRATION = OFF,
     CHECK_POLICY = OFF
GO

CREATE SERVER ROLE [Aireforge_Role] AUTHORIZATION sa;
GO

ALTER SERVER ROLE [Aireforge_Role] ADD MEMBER [Aireforge_user];
GO

--SELECT (on msdb only)

USE [msdb]
GO

CREATE USER [Aireforge_user] FOR LOGIN [Aireforge_user]
GO

CREATE ROLE [db_Aireforge_Role] AUTHORIZATION sa;
GO

ALTER ROLE [db_Aireforge_Role] ADD MEMBER [Aireforge_user];
GO

GRANT SELECT TO [db_Aireforge_Role]
GO

--VIEW SERVER STATE

USE [master]
GO

GRANT VIEW SERVER STATE TO [Aireforge_Role]
GO

--VIEW ANY DEFINITION

USE [master]
GO

GRANT VIEW ANY DEFINITION TO [Aireforge_Role]
GO

--CONNECT ANY DATABASE

GRANT CONNECT ANY DATABASE TO [Aireforge_Role]
GO

--EXEC xp_readerrorlog, for the failed logins and slow I/O checks

USE [master]
GO

CREATE USER [Aireforge_user] FOR LOGIN [Aireforge_user]
GO

GRANT EXEC ON xp_readerrorlog TO [Aireforge_user]
GO

--xp_readerrorlog also needs securityadmin membership. That role can grant server
--permissions and reset SQL logins, so it is left out here rather than granted by
--default. Uncomment it only if you want the failed logins and slow I/O checks, and
--accept what the role allows.
--ALTER SERVER ROLE [securityadmin] ADD MEMBER [Aireforge_user]
--GO

--SYSADMIN SERVER ROLE is deliberately not granted here.
--Without it, Studio cannot read SQL Server Agent service state, Database Mail
--configuration and accounts, or Agent operators, alerts, proxies and
--notifications, and it cannot apply any fix script Advisor generates. See
--"What needs sysadmin" above.

Permissions in detail