SQLBenchmarkPro Permissions Guide

SQLBenchmarkPro collects performance, configuration & schema information from Windows and SQL Server. Appropriate permissions must be granted both in Windows and in SQL Server for SQLBenchmarkPro to operate smoothly.

Choosing an appropriate level of SQL Server permissions involves deciding between the convenience of granting fewer high level permissions (eg global sysadmin permissions) versus granting more granular permissions.

With the above in mind, we have provided the following three options:

Permission Options

High level permission / Low complexity setup

Description

  • No need to configure NTFS share permissions, or any SQL Server role, or database permissions

Windows

  • Member of Windows Local Administrators Group on the SQL Server Host

SQL Server

  • Member of SQL Server sysadmins server role

Medium level permission / Medium complexity setup

Description

  • Requires configuration of NTFS share & directory permissions
  • SQL Server permissions are lowered below sysadmin but no need to configure database level permissions

Windows

  • Member of Windows Performance Monitor Users Group
  • Share trace logging directory, assign CHANGE permission to the share and MODIFY + READ + WRITE to the share's directory permissions

SQL Server

  • SQL Server CONTROL_SERVER permission (inherits ALTER_TRACE, VIEW SERVER STATE & VIEW ANY DEFINITION permission
  • GRANT EXECUTE ON [msdb].[dbo].[sp_get_composite_job_info]

Minimum level permission / Most complex setup

Description

  • Requires configuration of NTFS share & directory permissions
  • Requires SQL Server permissions to be configured at a per-database level

Windows

  • Member of Windows Performance Monitor Users Group
  • Share trace logging directory, assign CHANGE permission to the share and MODIFY + READ + WRITE to the share's directory permissions

SQL Server

  • Member of public server role
  • SQL Server ALTER_TRACE, VIEW SERVER STATE & VIEW ANY DEFINITION permissions (server roles)
  • GRANT EXECUTE ON [msdb].[dbo].[sp_get_composite_job_info]
  • Member of ddl_admin database role in every user database (reconfigure model database with ddl_admin role membership for DBs to be created in future)

Which accounts need these permissions?

The SQLBenchmarkPro 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 SQLBenchmarkPro

Once imported by SQLBenchmarkPro, SQLTrace files are deleted from the SQLTrace logging directory on the monitored SQL Server. Permissions required to delete imported SQLTraces depend on whether the SQLBenchmarkPro 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 SQLBenchmarkPro 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 SQLBenchmarkPro 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 SQLBenchmarkPro "run as" Windows domain account is always used to collect Windows Perfmon counters and delete SQLTrace (.trc) files after they have been imported into SQLBenchmarkPro

SQL Server permissions described in the above three permission setup options must be granted in SQL Server to the SQLBenchmarkPro 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