June 16, 2011 at 9:44 am
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]
June 20, 2011 at 7:43 am
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
June 20, 2011 at 7:58 am
quan23 (6/20/2011)
The security policy of all reports/folders is stored in the PolicyID field in the catalog table of the ReportServer databaseThe 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]
June 20, 2011 at 9:13 am
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:
August 10, 2022 at 6:42 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply