What are the required permissions for the sqlserver probe?

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

 

Instructions:

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