locked
How can we quesry User roles on each report in SSRS. RRS feed

  • Question

  • Please help. I would  like to get the Let of users access each report and their role on that report . How can we get this?

    Wednesday, April 4, 2012 2:17 PM

Answers

  • Hi md.k,

    Reporting Services uses two SQL Server relational databases to store report server metadata and objects. In this issue, the data you require are stored in the ReportServer Database. To achieve your goal, you can retrieve the all the report names from the dbo.Catalog table, retrieve the User names from the dbo.User table and Roles from the dbo.Roles table. Please refer to the steps below:

    1. Open SQL Server Management Studio (SSMS), connect to the “Database Engine” server type.
    2. Expand the “Databases” node, right-click on the Report Server database and click “New Query”. Copy and paste the following T-SQL text to the surface:
      SELECT     c.Name, u.UserName, r.RoleName, r.Description
      FROM         dbo.Catalog AS c INNER JOIN
                            dbo.Users AS u ON c.CreatedByID = u.UserID AND c.ModifiedByID = u.UserID CROSS JOIN
                            dbo.Roles AS r
      ORDER BY c.Name, u.UserName, r.RoleName
    3. If you want to obtain the users and roles for a specified report, you can add the following WHERE clause to the T-SQL query before the ORDER BY clause:
      WHERE (c.Name=’ReportName’)

    Reference:
    SSRS 2008 List All Reports, Users, Roles, and Security

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    • Marked as answer by Elvis Long Monday, April 9, 2012 4:28 AM
    Friday, April 6, 2012 8:44 AM

All replies

  • SELECT
        R.RoleName,
        U.UserName,
        C.Path,
    	CASE C.Type
    	WHEN 1 then 'Folder'
        WHEN 2 then 'Report'
        WHEN 5 then 'DataSourceDefinition'
        WHEN 8 then 'SharedDataSet'
        WHEN 6 then 'Report Model'
        else CAST (Type as varchar)
       end as RecordType
     
    FROM
        ReportServer.dbo.Catalog C WITH (NOLOCK)    --Parent
        JOIN
        ReportServer.dbo.Policies P WITH (NOLOCK) ON C.PolicyID = P.PolicyID
        JOIN
        ReportServer.dbo.PolicyUserRole PUR WITH (NOLOCK) ON P.PolicyID = PUR.PolicyID 
        JOIN
        ReportServer.dbo.Users U WITH (NOLOCK) ON PUR.UserID = U.UserID 
        JOIN
        ReportServer.dbo.Roles R WITH (NOLOCK) ON PUR.RoleID = R.RoleID

    Easy way is to just run this in the reportserverDB.  Or if you want to get fancy you can walk the tree using the ssrs webservice

    Chuck

    Wednesday, April 4, 2012 2:36 PM
  • Hi md.k,

    Reporting Services uses two SQL Server relational databases to store report server metadata and objects. In this issue, the data you require are stored in the ReportServer Database. To achieve your goal, you can retrieve the all the report names from the dbo.Catalog table, retrieve the User names from the dbo.User table and Roles from the dbo.Roles table. Please refer to the steps below:

    1. Open SQL Server Management Studio (SSMS), connect to the “Database Engine” server type.
    2. Expand the “Databases” node, right-click on the Report Server database and click “New Query”. Copy and paste the following T-SQL text to the surface:
      SELECT     c.Name, u.UserName, r.RoleName, r.Description
      FROM         dbo.Catalog AS c INNER JOIN
                            dbo.Users AS u ON c.CreatedByID = u.UserID AND c.ModifiedByID = u.UserID CROSS JOIN
                            dbo.Roles AS r
      ORDER BY c.Name, u.UserName, r.RoleName
    3. If you want to obtain the users and roles for a specified report, you can add the following WHERE clause to the T-SQL query before the ORDER BY clause:
      WHERE (c.Name=’ReportName’)

    Reference:
    SSRS 2008 List All Reports, Users, Roles, and Security

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    • Marked as answer by Elvis Long Monday, April 9, 2012 4:28 AM
    Friday, April 6, 2012 8:44 AM