locked
ReportServer DB query help RRS feed

  • Question

  • I found this query online. It shows the security settings for all items you have on SSRS. How can I filter this out so that it does not include Content Manager?

    SELECT
    Catalog.Path,
    Catalog.Name,
    Users.UserName,
    Catalog.Type
    FROM ReportServer.dbo.Catalog
    INNER JOIN ReportServer.dbo.Policies ON Catalog.PolicyID = Policies.PolicyID
    INNER JOIN ReportServer.dbo.PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID
    INNER JOIN ReportServer.dbo.Users ON PolicyUserRole.UserID = Users.UserID

    Thursday, May 15, 2014 1:33 AM

Answers

  • Hi,

    Use this query to exclude users in the Content Manager role.

    SELECT
    Catalog.Path,
    Catalog.Name,
    Users.UserName,
    Catalog.Type
    FROM ReportServer.dbo.Catalog
    INNER JOIN ReportServer.dbo.Policies ON Catalog.PolicyID = Policies.PolicyID
    INNER JOIN ReportServer.dbo.PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID
    INNER JOIN ReportServer.dbo.Users ON PolicyUserRole.UserID = Users.UserID
    INNER JOIN ReportServer.dbo.Roles ON PolicyUserRole.RoleID = Roles.RoleID
    AND Roles.RoleName <> 'Content Manager'
    

    Thanks.


    Tracy Cai
    TechNet Community Support

    Friday, May 16, 2014 1:28 AM