What are the required permissions for the sqlserver probe?

Document ID : KB000034640
Last Modified Date : 14/02/2018
Show Technical Document Details
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.

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.

Source:?http://docs.nimsoft.com/prodhelp/en_US/Probes/Catalog/sqlserver/4.8/2090461.html?

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
?