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].[sp_enumerrorlogs] and [master].[dbo].[xp_readerrorlog]

User Databases

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

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].[sp_enumerrorlogs] TO [##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

 

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