Need query of ReportServer database that retrieves report-specific security information

  • Hi,

    I need to query the ReportServer database in my environment to get security information for each report and report folder on the report server.

    I am especially interested in reports and report folders that do not inherit the security settings from the parent folder, but have overriden settings, ie. users and Windows groups that have been configured manually to access these specific folders/reports.

    Does anyone have a query like that?

    Thank you for any insights!

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • The security policy of all reports/folders is stored in the PolicyID field in the catalog table of the ReportServer database

    The PolicyRoot field in the catalog table defines whether an item has picked up the default settings from the level above (0) or has effectively created a new root by changing the settings (1)

    I don't have a complete bit of code to allow you to itterate through all your items, but if you start off by running this:

    select policyid from [ReportServer].[dbo].[catalog] where parentid is null

    This will tell you the policyID set at the Home folder level of your ReportManager hierarchy, and you can then search for any items with a different policyID, or for other items where the PolicyRoot is set to 1

    This will give you all the items which do not inherit the permissions from the level above which is the first part of your investigation, you'll then have to do some digging to find out how to find out what the permissions have been changed to...

    If I get a chance I'll have a look myself later on today

  • quan23 (6/20/2011)


    The security policy of all reports/folders is stored in the PolicyID field in the catalog table of the ReportServer database

    The PolicyRoot field in the catalog table defines whether an item has picked up the default settings from the level above (0) or has effectively created a new root by changing the settings (1)

    I don't have a complete bit of code to allow you to itterate through all your items, but if you start off by running this:

    select policyid from [ReportServer].[dbo].[catalog] where parentid is null

    This will tell you the policyID set at the Home folder level of your ReportManager hierarchy, and you can then search for any items with a different policyID, or for other items where the PolicyRoot is set to 1

    This will give you all the items which do not inherit the permissions from the level above which is the first part of your investigation, you'll then have to do some digging to find out how to find out what the permissions have been changed to...

    If I get a chance I'll have a look myself later on today

    Thank you, that is actually very helpful!

    Being able to find out which reports do not inherit their security settings from the parent folder will help me a lot in my investigation.

    I'm in the process of moving reports from one server to another, and being able to find these outliers so I can copy their permission settings, is great.

    Thanks again for your help, and if you have anything more to add to this, it would be appreciated.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • This should get you started, it basically returns a list of users against their roles for any items which have a different policy to the Home folder:

    declare @HomePolicyID uniqueidentifier

    set @HomePolicyID = (select policyid from catalog where parentid is null)

    select c.path, c.name, modifieddate, users.username, rolename

    from catalog c inner join

    policyuserrole pur on c.policyid = pur.policyid inner join users on pur.userid = users.userid

    inner join roles on pur.roleid = roles.roleid

    where

    c.policyid <> @HomePolicyID

    order by c.path, c.name;

    You would probably want to look at all items with a PolicyRoot of 1 rather than just looking at the HomePolicyID as this does, but it should give you the general idea of how policies relate to users and roles

    At the moment this script will pull back multiple rows for an individual user if they have more than one permission set per item, e.g. if they have both content manager and browser they will appear in the list twice, so you might want to split this bit of code out into a function to return a comma separated list or similar to get a single line per user per item

    I would also recommend checking out the RSscripter tool if you are looking at moving reports and permissions to a new location. It is quite a flexible tool for doing the actual heavy lifting once you are ready to transport things:

    http://www.sqldbatips.com/showarticle.asp?ID=62

  • Here Try this one - It EXCLUDES high level Folders but does differentiate all the ones that do not match their respective parents.

    SELECT (SELECT DISTINCT policyid FROM [EOC-RPTSQLP01].[ReportServer].[dbo].catalog WHERE parentid IS NULL) AS RootPolicyID, (SELECT x.PolicyID FROM [EOC-RPTSQLP01].[ReportServer].[dbo].catalog x WHERE x.ItemID = c.ParentID) AS ParentPolicyID,

    c.PolicyID, c.path, c.name, c.modifieddate, users.username, rolename

    FROM catalog c

    INNER JOIN policyuserrole pur ON c.policyid = pur.policyid

    INNER JOIN users ON pur.userid = users.userid

    INNER JOIN roles ON pur.roleid = roles.roleid

    WHERE c.policyid <> (SELECT x.PolicyID FROM [EOC-RPTSQLP01].[ReportServer].[dbo].catalog x WHERE x.ItemID = c.ParentID)

    AND c.Path LIKE '%/%/%'

    GO

    • This reply was modified 1 year, 6 months ago by  dkangel.
    • This reply was modified 1 year, 6 months ago by  dkangel.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply