Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SSRS 2005 report to show reports in report manager and users who can access the reports

Posted on 2014-09-26
2
204 Views
Last Modified: 2014-10-01
Hi Experts

I need a report from SSRS 2005 to show the reports available in report manager and the users who can access that report and there type of access levels.

Dave.
0
Comment
Question by:MrDavidThorn
2 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 40349694
SSRS uses two system databases. One of them is called reportserver and contains the info you're looking for.

Basically the Catalog table contains a list of the reports, Roles contains the different roles and Users contains the users that have been defined.  You'll need to go through Policies to link a role with a user.

Have a look at the following query:

SELECT 
	CAT.Name
	,U.UserName
	,ROL.RoleName
	,ROL.Description
	,U.AuthType
FROM dbo.Users U
INNER JOIN dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID
INNER JOIN dbo.Policies POLICY ON POLICY.PolicyID = PUR.PolicyID
INNER JOIN dbo.Roles ROL ON ROL.RoleID = PUR.RoleID
INNER JOIN dbo.Catalog CAT ON CAT.PolicyID = POLICY.PolicyID
ORDER BY CAT.Name

Open in new window

Please note that, although I could have written this query myself given some time, I didn't because I knew it already existed here (query 4): SQL Server Reporting Services 2008 R2 Report Server Database Helpful Queries
0
 

Author Closing Comment

by:MrDavidThorn
ID: 40354109
I've used

SELECT   E.Name,E.Path,  
CASE E.[Type]
      WHEN 1 THEN 'FOLDER'
      WHEN 2 THEN 'REPORT'
      WHEN 4 THEN 'REPORT'
END AS [Type],C.UserName, D.RoleName, D.[Description]
From dbo.PolicyUserRole A with (NoLock)
INNER JOIN dbo.Users c WITH (NoLock) on A.UserId = C.UserId
INNER JOIN dbo.Roles D WITH (NoLock) on A.RoleId = D.RoleId
INNER JOIN (Select * FROM dbo.Catalog E With (NoLock) Where [Type] Not In (3,5)
 ) E
On A.PolicyID = E.PolicyID
Order by e.NAME
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question