Granting sqlserver probe permissions

Document ID : KB000057266
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

In order to use the sqlserver probe without an sa account, an account must be created and permissions must be granted to that user. ?

All command are expected to be executed in SQL Server Management Studio.

This document applies to SQL Server 2005, 2008 and 2012.




Procedure

1. Change current database context to MASTER
USE [master]?
GO?

2. Create your user. ?This document uses "nimsoft_probe" as the probe's account. Replace all occurences of "nimsoft_probe" and "actualpasswordgoeshere" to settings of your choosing.
CREATE LOGIN [nimsoft_probe] WITH PASSWORD=N'actualpasswordgoeshere', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF?
GO?

3. This section grants all of the permissions
GRANT VIEW SERVER STATE TO [nimsoft_probe]?
GO?
USE [master]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
USE [model]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
USE [msdb]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
USE [ReportServer]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
USE [ReportServerTempDB]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
USE [tempdb]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
USE [SAMStore]?
GO?
CREATE USER [nimsoft_probe] FOR LOGIN [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[databases] TO [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[database_files] TO [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[internal_tables] TO [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[sysperfinfo] TO [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[filegroups] TO [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[partitions] TO [nimsoft_probe]?
GO?
use [master]?
GO?
GRANT SELECT ON [sys].[allocation_units] TO [nimsoft_probe]?
GO?
use [msdb]?
GO?
GRANT SELECT ON [dbo].[sysjobsteps] TO [nimsoft_probe]?
GO?
use [msdb]?
GO?
GRANT SELECT ON [dbo].[sysjobs] TO [nimsoft_probe]?
GO?
use [msdb]?
GO?
GRANT SELECT ON [dbo].[log_shipping_monitor_primary] TO [nimsoft_probe]?
GO?
use [msdb]?
GO?
GRANT SELECT ON [dbo].[log_shipping_monitor_secondary] TO [nimsoft_probe]?
GO?
use [msdb]?
GO?
GRANT SELECT ON [dbo].[sysjobhistory] TO [nimsoft_probe]?
GO?
use [msdb]?
GO?
GRANT SELECT ON [dbo].[syscategories] TO [nimsoft_probe]?
GO