Minimum Permissions

Permissions

SQLBench requires minimal permissions to monitor any SQL Server instance.

Sysadmin is NOT necessary and is NOT recommended.

The SQLBench Agent monitoring setup wizard will guide you through permissions setup, including providing implementation scripts but a description of permission requirements is provided below.

Windows (SQL Server Host)

    Windows Groups

  • Membership of Windows Performance Monitor Users Group

    • NTFS

  • Ownership of an NTFS directory for SQLTrace logging.

 

SQL Server

    Server Permissions

  • ALTER_TRACE

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

    • SQLAgent

  • Membership of SQLAgentOperatorRole role in MSDB database

  • GRANT EXECUTE ON [msdb].[dbo].[sp_get_composite_job_info]

  • GRANT SELECT ON [msdb].[dbo].[sysjobsteps] and [msdb].[dbo].[sysjobhistory]

    • SQLErrorLog

  • GRANT EXECUTE ON [master].[dbo].[xp_readerrorlog]

    • User Databases

  • Guest access in every user database (including model database to include DBs created in future)

Azure SQL Database

    Database User

  • Create a user for the Login used by your SQLBench Agent

    • Permissions

  • ALTER ANY DATABASE EVENT SESSION

  • VIEW DATABASE PERFORMANCE STATE

  • VIEW DEFINITION

CloudWatch API (host metrics)

A CloudWatch API Key and Secret Key with CloudWatchReadOnlyAccess and AWSRDSReadOnlyAccess permission policies is required for the SQLBench Agent to collect host metrics such as CPU Utilisation, Available Memory, Disk Space etc

Instructions for creating this key are available here

 

SQL Server

    Server Permissions

  • ALTER_TRACE

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

    • SQLAgent

  • Membership of SQLAgentUserRole role in MSDB database

  • GRANT SELECT ON [msdb].[dbo].[sysjobs] and [msdb].[dbo].[sysjobhistory]

    • User Databases

  • Guest access in every user database (including model database to include DBs created in future)

Script

An example script is provided below to grant necessary permissions for SQLBench to monitor a SQL Server instance.

Replace the text "##SQLBenchLogin##" with the name of the SQL Server Login or SQL Server Windows Login that SQLBench will connect to the instance with.

 

USE [master]
GO
/* To create & manage SQLTrace logs */
GRANT ALTER TRACE TO [##LoginName##]

/* To access server level DMVs */
GRANT VIEW SERVER STATE TO [##LoginName##]

/* To access database object definitions via sysobjects etc (but not table data) */
GRANT VIEW ANY DEFINITION TO [##LoginName##]

/* To enumerate & read the SQL Error log (requires a guest user in master) */
if not exists(select * from sys.database_principals where name = '##LoginName##')
CREATE USER [##LoginName##] FOR LOGIN [##LoginName##]
GRANT EXECUTE ON [sys].[xp_readerrorlog] TO [##LoginName##]

/* To access SQL Agent Jobs & their execution history */
use [msdb]
GO
if not exists(select * from sys.database_principals where name = '##LoginName##')
CREATE USER [##LoginName##] FOR LOGIN [##LoginName##]
EXEC sp_addrolemember 'SQLAgentOperatorRole', '##LoginName##'
GRANT EXECUTE ON [dbo].[sp_get_composite_job_info] TO [##LoginName##]
GRANT SELECT ON [msdb].[dbo].[sysjobsteps] TO [##LoginName##]
GRANT SELECT ON [msdb].[dbo].[sysjobhistory] TO [##LoginName##]
GO

/* Give access to user DBs */
declare @db nvarchar(1000), @s nvarchar(1000)
if object_id('tempdb..#dbs') is not null drop table #dbs
select name into #dbs from master..sysdatabases /* where name in ('','') */
select @db = min(name) from #dbs
while @db is not null
 begin
  select @s = N'use ['+@db+N'];
  if not exists(select * from sys.database_principals where name = ''##LoginName##'')
  CREATE USER [##LoginName##] FOR LOGIN [##LoginName##]'
  exec(@s)
  select @db = min(name) from #dbs where name > @Db
 end
GO


 

/* Connect to master database and create a login\password for SQLBench */
CREATE LOGIN [##LoginName##] WITH PASSWORD = 'enter your password'

/* Change connection to Azure SQL Database, create a DB user and grant it minimum permissions */

/* Create a user in the Azure SQL Database */
IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME ='[##LoginName##]')
CREATE USER [##LoginName##] FOR LOGIN [##LoginName##]

/* To create & manage event session logs */
GRANT ALTER ANY DATABASE EVENT SESSION TO [##LoginName##]

/* To access performance state DMVs */
GRANT VIEW DATABASE PERFORMANCE STATE TO [##LoginName##]

/* To view object definitions */
GRANT VIEW DEFINITION TO [##LoginName##]


 

USE [master]
GO
/* To create & manage SQLTrace logs */
GRANT ALTER TRACE TO [##LoginName##]

/* To access server level DMVs */
GRANT VIEW SERVER STATE TO [##LoginName##]

/* To access database object definitions via sysobjects etc (but not table data) */
GRANT VIEW ANY DEFINITION TO [##LoginName##]

/* To access SQL Agent Jobs & their execution history */
use [msdb]
GO
if not exists(select * from sys.database_principals where name = '##LoginName##')
CREATE USER [##LoginName##] FOR LOGIN [##LoginName##]
EXEC sp_addrolemember 'SQLAgentUserRole', '##LoginName##'
GRANT SELECT ON [msdb].[dbo].[sysjobs] TO [##LoginName##]
GRANT SELECT ON [msdb].[dbo].[sysjobhistory] TO [##LoginName##]
GO

/* Give access to user DBs */
declare @db nvarchar(1000), @s nvarchar(1000)
if object_id('tempdb..#dbs') is not null drop table #dbs
select name into #dbs from master..sysdatabases where name not in ('master', 'model', 'rdsadmin')
select @db = min(name) from #dbs
while @db is not null
 begin
  select @s = N'use ['+@db+N'];
  if not exists(select * from sys.database_principals where name = ''##LoginName##'')
  CREATE USER [##LoginName##] FOR LOGIN [##LoginName##]'
  exec(@s)
  select @db = min(name) from #dbs where name > @Db and name not in ('master', 'model', 'rdsadmin')
 end
GO


 

Which accounts need these permissions?

The SQLBench Agent service uses its "run as" Windows domain account (nominated during installation) to connect to the host Windows operating system (WinOS) for each monitored SQL Server to perform the following:

1. Collect Windows Performance Counter values via the DotNET PerformanceCounter API (requiring either Membership of Windows Performance Monitor Users Group or the Windows Local Administrators Group)

and

2. Delete SQLTrace files after they have been imported by SQLBench

Once imported by SQLBench, SQLTrace files are deleted from the SQLTrace logging directory on the monitored SQL Server. Permissions required to delete imported SQLTraces depend on whether the SQLBench Agent service "run as" Windows domain account is a member of the Local Administrators Group in the monitored SQL Server.

  • If the "run as" domain account is a member of the Local Administrators Group the administrative share on the root of the SQLTrace logging directory's disk volume (eg D$) is sufficient & there is no need to create a share or grant NTFS directory permissions on the SQLTrace logging directory

  • If the "run as" domain account is NOT a member of the Local Administrators Group, an NTFS share should be created on the SQLTrace logging directory with CHANGE permission assigned to the "run as" domain account and also MODIFY + READ + WRITE permissions to the SQLTrace logging directory

The same SQLBench Agent service "run as" Windows domain account can be used to connect to monitored SQL Server instances if "Windows Authentication" is chosen in the connection settings when using the SQLBench Configuration GUI to add a monitored SQL Server instance.

If "SQL Server Login" is chosen in the connection settings instead, this SQL Server Login is used when connecting to the monitored SQL Server instance. Note that the SQL Server Login is only used for connecting to SQL Server. The SQLBench "run as" Windows domain account is always used to collect Windows Perfmon counters and delete SQLTrace (.trc) files after they have been imported into SQLBench

SQL Server permissions described in the above three permission setup options must be granted in SQL Server to the SQLBench Agent's "run as" Windows domain account or the SQL Server Login, depending on whether Windows Authentication or SQL Server Login option has been chosen

© 2022 Copyright Terms Privacy