New SQLBench Agent - version 2022.08.02.06

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)

The below script implements permissions precisely required by the SQLBench Agent

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