Example SQL for any user's permitted RS folders and reports

Here is an example SQL that might be useful to others.
I was looking for a way to preview what folders and reports any other
RS user is permitted to see. The API only allows you to see the catalog
items viewable by the credentials used to call the API (Windows login),
Plus, the Windows delegation issue (2nd hop loses credentials) was a
huge challenge in using the API to get this list.
In addition to verifying permissions or to mimic what any user can see,
this can be used to quickly fill a treeview control with the available
folders and reports for any user.
The easiest way to do this was a query directly against the RS SQL
tables in the ReportServer db. Of course, the table structure and
contents could change in future releases or SP's, and this has only
been lightly tested, so there may be some slight inaccuracy. Look in
the Users table to see if RS has domain prefixes or not for your users.
Although someone has likely documented the TaskMask string logic
already, I will post more detail on those values separately.
Hope this can help someone else,
Steve Nyberg
SELECT
C.Name, C.Path, C.Description, U.UserName,
ModifiedDate = Max(C.ModifiedDate), C.Type,
TypeDesc = CASE C.Type WHEN 1 THEN 'folder' WHEN 2 THEN 'report' ELSE
'other' END
FROM
ReportServer.dbo.[Catalog] C
INNER JOIN ReportServer.dbo.Policies P ON C.PolicyID = P.PolicyID
INNER JOIN ReportServer.dbo.PolicyUserRole PUR ON P.PolicyID = PUR.PolicyID
INNER JOIN ReportServer.dbo.Roles R ON PUR.RoleID = R.RoleID
INNER JOIN ReportServer.dbo.Users U ON PUR.UserID = U.UserID
WHERE
C.Hidden = 0
AND (C.Type = 1 AND SUBSTRING(R.TaskMask, 7, 1)='1') OR (C.Type = 2
AND SUBSTRING(R.TaskMask, 3, 1)='1')
GROUP BY
C.Name, C.Path, C.Description, U.UserName, C.Type,
CASE C.Type WHEN 1 THEN 'folder' WHEN 2 THEN 'report' ELSE 'other' END
HAVING U.UserName = 'yourusername'
ORDER BY C.PathJust to clarify: I wouldn't base any critical code on this approach as it
is not supported by Microsoft.
Recently, there was a thread on this very subject:
http://www.msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=b4b65c16-c9c8-4872-85d9-c75badc0f53d
--
Adrian M.
MCP
"Steve Nyberg" <stevenyberg@.hotmail.com> wrote in message
news:1110213443.740852.227300@.l41g2000cwc.googlegroups.com...
> Here is an example SQL that might be useful to others.
> I was looking for a way to preview what folders and reports any other
> RS user is permitted to see. The API only allows you to see the catalog
> items viewable by the credentials used to call the API (Windows login),
> Plus, the Windows delegation issue (2nd hop loses credentials) was a
> huge challenge in using the API to get this list.
> In addition to verifying permissions or to mimic what any user can see,
> this can be used to quickly fill a treeview control with the available
> folders and reports for any user.
> The easiest way to do this was a query directly against the RS SQL
> tables in the ReportServer db. Of course, the table structure and
> contents could change in future releases or SP's, and this has only
> been lightly tested, so there may be some slight inaccuracy. Look in
> the Users table to see if RS has domain prefixes or not for your users.
>
> Although someone has likely documented the TaskMask string logic
> already, I will post more detail on those values separately.
> Hope this can help someone else,
> Steve Nyberg
> SELECT
> C.Name, C.Path, C.Description, U.UserName,
> ModifiedDate = Max(C.ModifiedDate), C.Type,
> TypeDesc = CASE C.Type WHEN 1 THEN 'folder' WHEN 2 THEN 'report' ELSE
> 'other' END
> FROM
> ReportServer.dbo.[Catalog] C
> INNER JOIN ReportServer.dbo.Policies P ON C.PolicyID = P.PolicyID
> INNER JOIN ReportServer.dbo.PolicyUserRole PUR ON P.PolicyID => PUR.PolicyID
> INNER JOIN ReportServer.dbo.Roles R ON PUR.RoleID = R.RoleID
> INNER JOIN ReportServer.dbo.Users U ON PUR.UserID = U.UserID
> WHERE
> C.Hidden = 0
> AND (C.Type = 1 AND SUBSTRING(R.TaskMask, 7, 1)='1') OR (C.Type = 2
> AND SUBSTRING(R.TaskMask, 3, 1)='1')
>
> GROUP BY
> C.Name, C.Path, C.Description, U.UserName, C.Type,
> CASE C.Type WHEN 1 THEN 'folder' WHEN 2 THEN 'report' ELSE 'other' END
>
> HAVING U.UserName = 'yourusername'
> ORDER BY C.Path
>