Redacted Parameters

lmacdonald

I am a SQL Monitor Admin and an SysAdmin on all the SQL Servers it monitors.  Why does it say redacted when I try to view the parameters of the execution plan?  Is this a setting somewhere?

Alex B

Hi @lmacdonald,

The team have delved further and found that we were not, in fact, using the permission and so everything was being redacted.  The developers have now made a fix for this, which should be available in the release this week (usually Wednesday) and I will update here again when that is available.

Kind regards,
Alex

lmacdonald

If I copy the "SELECT query_plan FROM sys.dm_exec_query_plan" statement and run that in management studio I can see the parameters.

Jon_Kirkwood

Hi @lmacdonald

Thanks for reaching out on the Redgate forums regarding your SQL Monitor query.

Certainly, there are some details which you can check regarding redacted details of execution plans.

In UserPermissionsMapper.cs there is a line - "Permissions.QueryPlans.CanViewParameters" for admin permissions. If you have an admin role in SQL Monitor then they should be able to see them.

Depending on what authentication type (BASIC, AD, OIDC) you are using there is another check you can do.

• If AD or OIDC check SELECT * FROM settings.AuthPrincipals AS ap​ for their user and see if PrincipalRole is 1 (admin) - or just get the result for the user for all columns and we can have a look
• If BASIC auth, then see if they are logging in with the admin user.
  

Hope this helps track down the settings to alter the behaviour in your Monitor deployment

lmacdonald

We are logging in using our AD account or Windows Authentication.  I was not able to find the UserPermissionsMapper.cs file.  Where is that file located, I searched the entire server that SQL Monitor is installed on.

My account is not called out specifically but I am in the dbas group that does have a PrincipalRole of 1.  However I might be in other groups that have lesser permissions, but I would think the most privileged account would trump all that right?

Jon_Kirkwood

hI @lmacdonald

Apologies for the misdirection - the UserPermissionsMapper.cs file is in our code and not accessible/modifiable for end-users.

This has been escalated within our technical support team and we have found permission under the admin user level for being able to view the parameters but were unable to see how that linked up with the area in the software actually doing the redaction.  We are escalating to the developers and will update this post when we have more information from the team.

It may be worth checking to see if you can add a login explicitly as a user and admin and see if they can then view the parameters. 

If they can and if so, we can have a look at the priority for privilege based on different groups perhaps.  If it doesn't work even when they are explicitly added, then something else is occurring which we will need to investigate further into.

lmacdonald

Great thanks.  Yes I tried the none group approach too already, that hadn't helped.

Alex B

Hi @lmacdonald,

The team have just released SQL Monitor 12.0.15 which should fix the issue.  You can download here: https://download.red-gate.com/checkforupdates/SQLMonitorWeb/SQLMonitorWeb_12.0.15.21593.exe

Kind regards,
Alex