Reporting Services allows many nested levels of folders, just like Windows, and when you apply security settings to a folder the settings flow down to child items - which can be other folders, reports, and items like Excel or Word files. However when you change security settings on a child item you disengage it from the parent, so that any changes from then on to the parent won't affect the child (unlike Windows security, where you can "inherit" the parent settings and add child settings).
In my mind this makes security settings in Reporting Services hard to keep track of. Items either inherit the parent security settings, or have their own. This creates a problem if you want to change security settings on a global level, as if you make a change to a parent, any children that have seperate or custom security settings will not reflect the changes. Maintaining lots of different security settings on folders and reports could also become a maintenance problem very quickly.
However, the security settings in Reporting Services are very thorough. If you do not explicitly grant access to a report or folder to a user, that user will not even be able to see a link to the report. Manually browsing or typing in the URL to the report or folder won't get them there either - it's like the item does't exist! This can be helpful in some circumstances.
So I came up with the following 2 queries (and went on to make them into a Reporting Services report) that will show the items that do not inherit parent security and allow you to keep track of them, for the times when you absolutely, positively, must use Report Server security settings.
The first query returns the items on the Report Server that have custom security settings (to run this, you'll need to have access to the ReportServer database):
Disclaimer: use at your own risk. The following queries work for me - I don't make any guarantees for anyone else.
USE ReportServer
GO
-- *********************************************************
-- Query 1: list all items that have security changed from parent item
-- *********************************************************
SELECT C.ItemID, --primary key and identifier, UNIQUEIDENTIFIER, NOT NULL
C.Path, --full path from root, which can be used in an expression like
--"http:///Reports/Pages/Folder.aspx?ItemPath=X" (X needs to be URL-encoded), NVARCHAR(425), NOT NULL
C.[Name], --name of item, NVARCHAR(425), NOT NULL
C.ParentID, --ID of parent (links back to ItemID), UNIQUEIDENTIFIER, can be NULL
C.Type, --type of item in the "Catalog" table - DataSource = 5, Folder = 1, LinkedReport = 4, Report = 2, Resource = 3, Unknown = 0
--else (e.g. PDF, DOC), INT, NOT NULL
C.Hidden --1 for hidden, 0 for visible, BIT, can be NULL
FROM --get data from the Catalog table
dbo.[Catalog] C WITH (NOLOCK)
WHERE --don't get user folders ("My Reports")
(NOT (C.Path LIKE N'/Users Folders%')) AND
--only get changed items: Catalog.PolicyRoot setting of 1 means that the item has
--specific permissions set, 0 means it inherits the parent settings
(C.PolicyRoot = CONVERT(BIT, 1)) AND
--don't get the root item (top-level folder)
(C.ParentID IS NOT NULL)
I would not recommend querying the live ReportServer database, but rather copying the data out to another database on some sort of schedule for querying. You never know what Reporting Services is doing under the hood with it's own database.
That said, the query above returns data from the "Catalog" table for items (folders, reports, data sources, etc.) that have custom security settings. The query below will return the users that have access, given an ItemId:
DECLARE @ItemId UNIQUEIDENTIFIER --item to search for
SET @ItemId = ''
-- *********************************************************
--Query 2: list users who have access to a specific item
-- *********************************************************
SELECT DISTINCT
C.Path, U.UserName
FROM dbo.[Catalog] C WITH (NOLOCK) INNER JOIN
dbo.Policies P ON C.PolicyID = P.PolicyID INNER JOIN
dbo.PolicyUserRole PUR ON P.PolicyID = PUR.PolicyID INNER JOIN
dbo.Users U ON PUR.UserID = U.UserID
WHERE --get the passed item only
(C.ItemID = @ItemId)
Used together, these two queries can help keeping track of which items on a Report Server have custom security settings. Enjoy!
UPDATE: Updated Item Types in 1st query thanks to Lutz Roeder's .NET Reflector