6

Is there any way I can list (either using a SSRS report or querying through SQL code), the Username or Group security that has been assigned against root folders, sub-folders or reports ?

I've been asked to identify as an audit, which AD groups have access to all root folders and sub-folders within that root folder, or even down to the individual report level - of who has access!

How do I accomplish this request?

Learner74
  • 143
  • 1
  • 3
  • 9

1 Answers1

14

You can query the ReportServer database for this information, e.g.

SELECT 
    CASE [Catalog].[Type]
        WHEN 1 THEN 'Folder'
        WHEN 2 THEN 'Report'
        WHEN 3 THEN 'Resource'
        WHEN 4 THEN 'Linked Report'
        WHEN 5 THEN 'Data Source'
        WHEN 6 THEN 'Report Model'
        WHEN 8 THEN 'Shared Dataset'
        WHEN 9 THEN 'Report Part'       
    END AS CatalogType,
    [Catalog].[Type]
    --, [Catalog].ItemID 
    ,[Catalog].Name
    , Roles.RoleName
    , Users.UserName
FROM PolicyUserRole 
INNER JOIN Roles ON PolicyUserRole.RoleID = Roles.RoleID
INNER JOIN Policies ON PolicyUserRole.PolicyID = Policies.PolicyID
INNER JOIN Users ON PolicyUserRole.UserID = Users.UserID
INNER JOIN [Catalog] ON PolicyUserRole.PolicyID = [Catalog].PolicyID
ORDER BY 
    1
    , [Catalog].ItemID
    , [Catalog].Name
    , Roles.RoleName
    , Users.UserName
Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • 1
    I'm not 100% sure what Catalog type 3 represents - I think it can be used for any other resource not listed, e.g. uploaded files. There's very little documentation on this. Also note that querying ReportServer database directly is not supported by Microsoft and the schema and/or meaning of these values could change in later versions. – Nathan Griffiths Jun 12 '13 at 19:21
  • @Nathan - are these types defined in ReportServer somewhere? – TrailJon Mar 21 '16 at 16:25
  • @TrailJon Back when I wrote this, they weren't - not sure about more recent versions. – Nathan Griffiths Mar 22 '16 at 07:01