Health Checks

To allow Aireforge the ability to run health checks on servers, we recommend creating a designated Aireforge login with the permissions below enabled.

Feel free to review the permissions and their corresponding Health Checks and alter these to your requirements; a script is provided below that will set up the Aireforge login as required.

Permissions Required

CUSTOM SERVER ROLE
  • Auto Close Enabled
  • Auto Shrink Enabled
  • Database Free Space <5%
  • Database Mail Not Running
  • Full Recovery Mode With No Log Backups
  • Identity Column <5% Free Keys
  • Query Store In Unexpected State
  • Running Values Don't Match Config Values
  • Suspect Pages Found
  • Transaction Log Has <5% Free
  • VIEW SERVER STATE
  • Missing Indexes
  • More tempdb Data Files Than Cores
  • Deadlocks Occurred
  • Maximum Memory Greater Than Available
  • High Memory Usage
  • IO Stalls - sysdatabases (Requires CONNECT DATABASE for other databases)
  • AlwaysOn Replication Falling Behind - sysdatabases (Requires CONNECT DATABASE for other databases)
CONNECT ANY DATABASE
  • Disabled Indexes
  • Identity Column Less Than 10% Free Keys
  • Database Less Than 10% Free
  • Transaction Log Less Than 10% Free
  • IO Stalls
  • Query Store in an unexpected state
  • Untrusted FKs
  • Untrusted Constraints
  • AlwaysOn replication falling behind (Requires VIEW SERVER STATE)
  • SELECT (On Database msdb Only)
  • Enabled Jobs With No Schedule
  • Enabled Jobs With Disabled Schedule
  • Unsent Database Mail
  • EXEC agent_datetime
  • Failed SQL Server Agent Jobs
  • EXEC XP_ReaderErrorLog
  • Failed Logins
SYSADMIN SERVER ROLE
  • Excessive VLFs
  • Last Known DBCC CHECKDB > 30 Days
  • SQL Agent Service Not Running

Script: Create Aireforge_Role

--CUSTOM SERVER ROLE (minimum privileges)
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

--EXEC agent_datetime

use [msdb]

grant exec on agent_datetime to [db_Aireforge_Role]
GO

--VIEW SERVER STATE

use [master]

GRANT VIEW SERVER STATE TO [Aireforge_Role]
GO

--EXEC XP_ReaderErrorLog

use [master]

go
create user [Aireforge_user] for login [Aireforge_user]
GO
grant exec on xp_readerrorlog to [Aireforge_user]
GO

--CONNECT TO ANY DATABASE

GRANT CONNECT ANY DATABASE TO [Aireforge_Role]
GO

--SYSADMIN SERVER ROLE

EXEC master..sp_addsrvrolemember @loginame = N'Aireforge_user',
                                 @rolename = N'sysadmin'
GO