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