Alerts of sqlserver probe

[Miller Echagarreta]

Hi all.
I am testing the monitor for sqlserver probe, and we see this alarms. The customer indicate that the user of the DB have the permission respective and I don´t know why this alerts is generated.

Profile: ProfileDB-XXX-Pruebas/fg_freeSpace_with_avail_disk/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named '#temptab' in the database.

 Profile: ProfileDB-XXX-Pruebas/logfile_usage_with_avail_disk/Run Query - DB Provider: Code=0x0x80004005 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal "Monit_prm" is not able to access the database "SSISDB" under the current security context.

[Kathy Maguire]

Miller -

I believe that the KB111971 : UIM sqlserver probe the user does not have permission to perform this action for checkpoints  addresses this issue.

------------------------------
Kathy Maguire
Technical Support Engineer 4
Broadcom
------------------------------

[Miller Echagarreta]

Thanks Kathy I will test the KB.​​​

[Miller Echagarreta]

Hi Kathy, I have other server with sqlserver and we have try monitoring all DB's, but for any reason the user "usr_UIM" not working. We check the permission in SQL and the prerrequisites but we don't see where is the problem.

This is the alarms from sqlserver
​

Profile: ProfileX/lock_timeouts/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][SQL Server Native Client 11.0][SQL Server]VIEW SERVER STATE permission was denied on object 'server', database 'master'.

 Profile: ProfileX/buf_cachehit_ratio/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][SQL Server Native Client 11.0][SQL Server]VIEW SERVER STATE permission was denied on object 'server', database 'master'.

 Profile: ProfileX/login_count/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][SQL Server Native Client 11.0][SQL Server]VIEW SERVER STATE permission was denied on object 'server', database 'master'.

 Profile: ProfileX/deadlocks/Run Query - DB Provider: Code=0x0x80040e14 Source=Microsoft OLE DB Provider for ODBC Drivers Description=[Microsoft][SQL Server Native Client 11.0][SQL Server]VIEW SERVER STATE permission was denied on object 'server', database 'master'.

This is the permission that was configured in user sql

We used this link for to configure sql user ---->

https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes#sqlserver(SQLServerMonitoring)ReleaseNotes-AccessPrerequisites

What other configuration should I do? I am not an expert in SQL

[Kathy Maguire]

Miller -

The following Knowledge Documents contain sample SQL scripts that should help to set the proper permissions for the usr_UIM user to assure that the checkpoint queries that do not require db admin permissions will execute successfully:

KB34659 : What permissions are needed for a SQL user to run the sqlserver probe?
KB34640 : What are the required permissions for the sqlserver probe?

The SQL script in the first document applies the permission changes to all of the databases found on the MSSQL Server.  The second document contains different SQL scripts depending on the version of MSSQL Server (note the script for version 11 are applicable for MSSQL Server version 11 and higher) but these scripts are only applied to the system databases.

------------------------------
Kathy Maguire
Technical Support Engineer 4
Broadcom
------------------------------

[Issac08]

Please check the below . Have you tried configuring using SA account ? if possible test in non prod environment 

For SQL Server versions 9 and 10: 

set VIEW SERVER STATE permission on master database. Also GRANT SELECT permission to a user for these tables 
Table Names 
master.sys.databases 
master.dbo.sysperfinfo 
msdb.dbo.sysjobsteps 
msdb.dbo.sysjobs 
.sys.database_files 
.sys.partitions 
.sys.allocation_units 
.sys.internal_tables 
.sys.filegroups 

For SQL Server version 8: 

GRANT SELECT permission to a user for these tables 

Table Names 
master.dbo.sysprocesses 
master.dbo.sysperfinfo 
master.dbo.sysdatabases 
msdb.dbo.backupset 
master.dbo.sysfiles 
master.dbo.sysindexes 
master.dbo.sysfilegroups

https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes

Check Access Prerequisites tab