sqlserver probe view server state permission to public

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)

Issue/Introduction

Got this error message for some of the monitors in sqlserver probe.

"The user does not have permission to perform this action"

- active_users

- buf_cachehit_ratio

- login_count

- server_cpu

- transactions

The sql login already granted VIEW SERVER STATE permission.

 

Unusually, after executing "GRANT VIEW SERVER STATE TO [Public]", the permission issue was resolved.

Why does sqlserver probe requires view server state permission to public?

 

 

Environment

Release : 20.4.x

sqlserver probe :5.45

Resolution

View server state permission to public is not required for sqlserver probe

Check that view server state permission to the specific user was given correctly

Steps to check;-

1. Open Microsoft SQL Server Management Studio.In the Object explorer open Security.
2. Under Security open Logins.
3. Right click on the login name you are using for sqlserver probe.
4. From the options Select Properties.
5. The login Properties page will open ,Select Securables.
6. In the Securables in the permission section check View Server State permission is granted or not.

If not granted, Please click on grant and press ok as shown in above snapshot

Check if granting only this to the user, without granting view server state permission to public resolves the issue

 

In this use case 

Though the VIEW SERVER STATE was granted to the user. The VIEW SERVER STATE was denied on PUBLIC role. This lead to a deny to the user.

Once Deny was unchecked on PUBLIC role, the issue is resolved.

Additional Information

UIM sqlserver probe - MS SQL Server permissions error - The user does not have permission to perform this action.

 

What permissions are needed for a SQL user to run the sqlserver probe?