How to import / export user account in Reporting Services?

Question

There is a table [ReportServer].[dbo].[User]. But what is the relationship with other tables? And how can I generate the values inside?

Answer 1

The values in this table are based on the permissions that have been setup in the report hierarchy. The most common way to manage this is through Report Manager under the Properties -> Security.

dbo.Users can be joined to dbo.PolicyUserRole. The table dbo.PolicyUserRole can then be joined to dbo.Roles, dbo.Policies, and dbo.Catalog.

Here is a query that provides all of the reports/models/folders and all of their users and related permissions:

SELECT c.Path
    ,u.UserName
    ,r.RoleName
    ,r.Description
FROM 
    dbo.Catalog c
    INNER JOIN dbo.PolicyUserRole pur ON c.PolicyID = pur.PolicyID
    INNER JOIN dbo.Users u ON pur.UserID = u.UserID
    INNER JOIN dbo.Roles r ON pur.RoleID = r.RoleID