Redacted Parameters

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?

Best Answer

  • Alex BAlex B Posts: 1,045 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?

Answers

  • lmacdonaldlmacdonald Posts: 7 New member
    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_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 118 Silver 1

    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


    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • lmacdonaldlmacdonald Posts: 7 New member
    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_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 118 Silver 1

    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.


    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • lmacdonaldlmacdonald Posts: 7 New member
    Great thanks.  Yes I tried the none group approach too already, that hadn't helped.
  • Alex BAlex B Posts: 1,045 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.