Script which reports a user has access to in reporting services

  • martin.griffiths 83810

    SSC Enthusiast

    Points: 110

    Does anyone have a script I can run to return a list of all reports a user has access to please?

  • Sue_H

    SSC Guru

    Points: 89874

    martin.griffiths 83810 wrote:

    Does anyone have a script I can run to return a list of all reports a user has access to please?

    ------------------------------------------

    They permissions query for objects in SSRS are all pretty much the same, along the lines of:

    SELECT U.UserName,

    C.Path,

    C.Name as Report

    FROM dbo.PolicyUserRole PR

    INNER JOIN dbo.Policies P

    ON PR.PolicyID = P.PolicyID

    INNER JOIN dbo.Users U

    ON PR.UserID = U.UserID

    INNER JOIN dbo.Roles R

    ON PR.RoleID = R.RoleID

    INNER JOIN dbo.Catalog C

    ON PR.PolicyID = C.PolicyID

    WHERE C.Type = 2

    ORDER BY U.UserName;

    Catalog type = 2 is for reports. You can change that, comment it out if you want to see more permissions, you can add a where filter for username if needed.

    Sue

  • martin.griffiths 83810

    SSC Enthusiast

    Points: 110

    Thank you Sue. Only problem is we've granted access by Active Directory security groups so we'd have to somehow do a adsi query off this I guess?

  • Sue_H

    SSC Guru

    Points: 89874

    Yeah...SSRS isn't going to know who are members of that group or keep that up to date since it changes. SSRS just knows what principal, user or group, has been granted permissions.

    Sue

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply