new Permissions needed for monitoring
FrankSchneider
Posts: 8
Today i have upgraded SQLMonitor to Version 6.
The installation was successful and without any issues.
Afterwards i try to resume monitored which was suspended before installation.
Unfortunately i got the error :
User 'xxxyyyyyyyyyyyy' does not have permission to run DBCC DBINFO.
To solve this i had to set sysadmin role to the "Monitor" - User ( 'xxxyyyyyyyyyyyy' ).
Is there a way to get the monitoring running without giving the sysadmin role ?
Due to the risk we dont want to have monitoring account with such high permissions !
regards, Frank
The installation was successful and without any issues.
Afterwards i try to resume monitored which was suspended before installation.
Unfortunately i got the error :
User 'xxxyyyyyyyyyyyy' does not have permission to run DBCC DBINFO.
To solve this i had to set sysadmin role to the "Monitor" - User ( 'xxxyyyyyyyyyyyy' ).
Is there a way to get the monitoring running without giving the sysadmin role ?
Due to the risk we dont want to have monitoring account with such high permissions !
regards, Frank
Comments
You shouldn't have to enable sysadmin permissions - here are the details of the requirements:
https://documentation.red-gate.com/disp ... ermissions
Let me know if you need further details
thanks for your answer.
Unfortunately i am not able to figure out which are the specific flags :
sysadmin role required for Integrity check overdue alerts and to allow SQL Monitor to turn on the deadlock trace flag (this flag is required for Deadlock alerts to be raised; you can turn on the flag manually if you don't want to enable sysadmin permissions).
We are monitoring a 2 node AlwaysOn Cluster with 4 Instances.
In every Instance the following Commands were executed :
GRANT VIEW ANY DEFINITION TO [xxxxxyyyyyyyyyyyyyy]
GRANT VIEW SERVER STATE TO [xxxxxyyyyyyyyyyyyyy]
GRANT VIEW DATABASE STATE TO [xxxxxyyyyyyyyyyyyyy]
But i still got the error (after removing the sysadmin role from the monitoring account) :
IntegrityCheckSampler Incorrect credentials or insufficient permissions SqlException User 'xxxxxyyyyyyyyyyyyyy' does not have permission to run DBCC DBINFO.
Could you please explain how to disable the IntegrityCheckSampler ?
SQLMonitor - Version below Version 6 was running fine over the last 2 years without the sysadmin - role.
regards, Frank
We've just purchased the product, and upgraded at the same time, to find all the SQL monitoring has stopped.
We really don't want to give the monitoring account sysadmin, that's a backwards step?
Without sysadmin - role the Monitoring doesnt work.
Hope that Red-Gate can help with an solution.
Redgate did provide me with a workaround yesterday, which fixes the issue, if you are happy to live without the db integrity check alert.
Not sure we want to give sysadmin to a monitoring user - we use a least privilege principle, a monitoring account shouldn't need sysadmin.
The solution to this is below, as AlphonsoDelard mentions this does disable the integrity check alert but will allow the rest of the monitoring to continue.
The workaround for this is to disable the integrity check from within the sampler. Whilst the alert is disabled SQL Monitor still collects this data in case you want to turn the alert on and then have historical data.
To turn this off navigate to the sql monitor install directory <C:Program FilesRed GateSQL MonitorBaseMonitor> and open the ConfigurationFileEditor.exe. Click to load a config file and select <C:ProgramDataRed GateSQL MonitorRedGate.Response.Engine.Alerting.Base.Service.exe.settings.config>.
You can select the [cluster].[sqlserver].[database].[integritycheck] and select disabled from the dropdown. Then restart the SQL Monitor base monitor and this should stop this sampler running.
Dan Bainbridge
Product Support Engineer | Redgate Software
Hi Dan,
I tried to disable integrity check but I cannot find the config file <C:ProgramDataRed GateSQL MonitorRedGate.Response.Engine.Alerting.Base.Service.exe.settings.config> that you mentioned above anywhere. Could you provide more detail how to disable the Integrity check. I cannot grant sysadmin right for monitor account.
Thank you,
The conf file to load:
"C:\ProgramData\Red Gate\SQL Monitor\RedGate.SqlMonitor.Engine.Alerting.Base.Service.exe.settings.config"
Conf to disable and save:
[cluster].[sqlserver].[database].[integritycheck]