Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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

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
MrDavidThorn
Asked:
MrDavidThorn
1 Solution
 
ValentinoVBI ConsultantCommented:
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
 
MrDavidThornAuthor Commented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now