What are the required permissions for the sqlserver probe?

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The following permissions are required and are grouped by SQLServer version. Example scripts are provided at the end as a guideline on how to grant permissions to SQLServer.

 

Environment

Release:
Component: UIMSQS

Resolution

For SQL Server versions 9, 10, and 11 set VIEW SERVER STATE permission on master database. Also, map the user for the following databases:

• master
• model
• msdb
• ReportServer
• ReportServerTempDB
• tempdb

User mapping is required for the following tables:

• master.sys.databases
• master.dbo.sysperfinfo
• msdb.dbo.sysjobsteps
• msdb.dbo.sysjobs
• msdb.dbo.syscategories
• msdb.dbo.log_shipping_monitor_secondary
• msdb.dbo.log_shipping_monitor_primary
• msdb.dbo.sysjobhistory
• .sys.database_files
• .sys.partitions
• .sys.allocation_units
• .sys.internal_tables
• .sys.filegroups

For Windows authentication, map the user for access to SQL server, and to the Niscache folder of the file system on which the CA UIM robot is installed.

Note: When you grant permission to the user, the checkpoints?fg_freespace_with_avail_disk and logfile_usage_with_avail_disk does not work for the non-Administrator user.

This is for version 11.
This is assuming the user is infra_nimsoft; you will need to change the user_name accordingly.
Run the following script.
--For SQL Version 11 use the following
USE MASTER;
GRANT VIEW SERVER STATE TO infra_nimsoft
GRANT SELECT ON OBJECT::master.sys.databases TO infra_nimsoft
GRANT SELECT ON OBJECT::master.dbo.sysperfinfo TO infra_nimsoft

USE MSDB
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsteps TO infra_nimsoft
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO infra_nimsoft
GRANT SELECT ON OBJECT::msdb.dbo.syscategories TO infra_nimsoft
GRANT SELECT ON OBJECT::msdb.dbo.log_shipping_monitor_secondary TO infra_nimsoft
GRANT SELECT ON OBJECT::msdb.dbo.log_shipping_monitor_primary TO infra_nimsoft
GRANT SELECT ON OBJECT::msdb.dbo.sysjobhistory TO infra_nimsoft

GRANT SELECT ON OBJECT::.sys.database_files TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.partitions TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.allocation_units TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.internal_tables TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.filegroups TO infra_nimsoft
--End of Script for 11

--For SQL Server versions 9 and 10

USE MASTER;
GRANT VIEW SERVER STATE TO infra_nimsoft
GRANT SELECT ON OBJECT:: master.sys.databases TO infra_nimsoft
GRANT SELECT ON OBJECT::master.dbo.sysperfinfo TO infra_nimsoft

USE MSDB?
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsteps TO infra_nimsoft
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO infra_nimsoft

GRANT SELECT ON OBJECT::.sys.database_files TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.partitions TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.allocation_units TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.internal_tables TO infra_nimsoft
GRANT SELECT ON OBJECT::.sys.filegroups TO infra_nimsoft
--End of Script for 9 and 10
For eight

--Version 8
USE master;
GRANT SELECT ON master.dbo.sysprocesses TO infra_nimsoft
GO
GRANT SELECT ON master.dbo.sysperfinfo TO infra_nimsoft
GO
GRANT SELECT ON master.dbo.sysdatabases TO infra_nimsoft
GO
USE MSDB;
GRANT SELECT ON msdb.dbo.backupset TO infra_nimsoft
GO
--End of Script for 8