none
Reproduce "Effective Rights" function in an SQL query? RRS feed

  • Question

  •      I have a need to reproduce the PS 2013 "Effective Rights" function (Server Settings/Manage Users) in an Excel VB macro, i.e., supply a "Resource Name" and a "Project Name", then get back a list of category permissions, if any.

        Has anyone done this, or is there any document in Microsoft's Tech library that might guide me in constructing a query?

        Thanks in advance for any assistance!

    JTC


    JAckson T. Cole, PMP, MCITP


    Wednesday, May 10, 2017 12:28 PM

Answers

  • Hello,

    You won't find anything documented for this using SQL as it wouldn't be supported to query this detail via T-SQL as this data is in tables that are not supported for direct access via T-SQL. You would need to use the APIs, CSOM or the PSI to access this is a supported way. For the security details (categories etc.) you will need to use the PSI as there is no class / methods in CSOM to do this.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, May 10, 2017 12:36 PM
    Moderator
  • Hi,

    as Paul wrote, it is not supported, so I will not paste any query here. Since I had to do something similar in the past (but this was for a one-time query), I may be able to help offline. If you are interested, please send me an email (link to my HP in my profile). There is a risk doing so, since data is only available in schema pub and queries may caues deadlocks ... But we can discuss offline, if you want to.

    Regards
    Barbara

    • Marked as answer by Jackson Cole Wednesday, May 10, 2017 6:13 PM
    Wednesday, May 10, 2017 6:03 PM
    Moderator

All replies

  • Hello,

    You won't find anything documented for this using SQL as it wouldn't be supported to query this detail via T-SQL as this data is in tables that are not supported for direct access via T-SQL. You would need to use the APIs, CSOM or the PSI to access this is a supported way. For the security details (categories etc.) you will need to use the PSI as there is no class / methods in CSOM to do this.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, May 10, 2017 12:36 PM
    Moderator
  •    Yes, I knew that even "read-only" access to all the DB was frowned upon, but our site has granted a few of us that ability to build reports, like a catalog of the security groups and their members.  That set of "MSP_Web_Security" tables is a grand, convoluted mess to be sure! :>}

         I have asked a previous question regarding  a possible "Project Permissions" report, which would involve much the same excursion though various tables, and no one ever responded to that. Guess I know why, now!

         Thanks!

    JTC


    JAckson T. Cole, PMP, MCITP

    Wednesday, May 10, 2017 3:56 PM
  • Hi,

    as Paul wrote, it is not supported, so I will not paste any query here. Since I had to do something similar in the past (but this was for a one-time query), I may be able to help offline. If you are interested, please send me an email (link to my HP in my profile). There is a risk doing so, since data is only available in schema pub and queries may caues deadlocks ... But we can discuss offline, if you want to.

    Regards
    Barbara

    • Marked as answer by Jackson Cole Wednesday, May 10, 2017 6:13 PM
    Wednesday, May 10, 2017 6:03 PM
    Moderator