Need to query SSRS database for report access

Question

I would like to know if there is a way to return in a SQL (2005) query (for all reports): Report ID, Report Name, Report Path, User and Group Security, Datasources.

I know that I can go to my report server page and go to Properties > Security, but we have over 120 reports and I want to see if there is an easier way to get the information I need.

Thanks in advance!

Answer 1

A. Never ever query RS Database directly until you know what are you doing. The queries could acquires lock which could affect overall performance of RS.

B. To know about report execution stats, you can use ExecutionLog view in RS 2008 onwards or following query with NOLOCK Hint.

Select CAST(C.Name AS VARCHAR(20)) [Name], 
E.ReportID, 
E.InstanceName, 
E.UserName, 
E.RequestType, 
E.Format, 
E.Parameters, 
E.TimeStart, 
E.TimeEnd, 
E.TimeDataRetrieval, 
E.TimeProcessing, 
E.TimeRendering, 
E.Source, 
E.Status, 
E.ByteCount, 
E.[RowCount] 
from executionlog E WITH (NOLOCK) inner join catalog C WITH (NOLOCK)   
on E.ReportID = C.ItemID 

C. RS exposes almost all the functionality via SOAP APIs. For example this sample published my MS shows how to get security information on Report Items http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008R2%21RSPermissions%20Sample%20Application&referringTitle=Home

To know more about RS SOAP APIs please see http://msdn.microsoft.com/en-us/library/ms154052.aspx

Answer 2

You can query the ReportServer database directly using something like SSMS. Most of what you're looking for can be found directly in the dbo.Catalog table:

SELECT *
FROM dbo.Catalog
WHERE Type = 2; -- 2 = Reports, 5 = Data Sources