How to get available SSRS reports from a website

Question

We have set up SQL Reporting Services 2012 and already have some reports installed and running.

We have set up permissions for user roles to access certain reports through active directory and everything works fine when viewing these reports from the SSRS webservice

Now we want to display these reports from inside a website deployed in another server, made in React, which should only list the reports the currently logged user has access to in a more beautiful user interface than what SSRS provides.

How can we determine, from the website, the list of links with the reports to show? Keep in mind users with different permissions should only see the links they have permission to access

Harry Harry · Accepted Answer · 2017-12-19T23:25:05

How about something like this against the reportserver? You would pass the logged in user name to this query to restrict the reports

SELECT C.Name 
      ,U.UserName 
      ,R.RoleName 
      ,R.Description 
      ,U.AuthType 
  FROM Reportserver.dbo.Users U 
  JOIN Reportserver.dbo.PolicyUserRole PUR 
    ON U.UserID = PUR.UserID 
  JOIN Reportserver.dbo.Policies P 
    ON P.PolicyID = PUR.PolicyID 
  JOIN Reportserver.dbo.Roles R 
    ON R.RoleID = PUR.RoleID 
  JOIN Reportserver.dbo.Catalog c 
    ON C.PolicyID = P.PolicyID 

ORDER BY U.UserName

How to get available SSRS reports from a website

1 Answers