Review Security Drift in Reporting Services

Reporting Services deployments aren’t exactly the latest fashion, so any you stumble upon are probably a little dated and in need of TLC. One place to review would be the security.

Security in SSRS comes in two flavours:

• ⚙️ System level roles (under Site Settings) to define site-wide permissions – all visible in one place
• 📄 Item level roles define permissions associated with the objects (reports, folders, etc.) which can be cascaded or customised

Item level roles are what we’re digging into here. Before we start, it’s worth defining a simple security model so it’s applied consistently. Let’s be real, the instance might not have a long term future but let’s do it right at least, eh?

A few suggestions to consider:

• 📏 Policy level: applying at a folder level helps with consistency without being too granular such as report-specific. Depending on sensitivity of data sources/sets there may be exceptions needed
• 🔍 Account scope: using groups for security instead of individuals moves the maintenance outside of the report server and also helps with joiners / leavers if membership policies are maintained
• ✍️ Documenting: always record any bespoke permissions and why. An external document or the folder Description should suffice

Now to look for the changes. By default, security is inherited for child objects. This can be broken when permissions need to be tailored, however doing this will result in security changes for the parent not being reflected in child objects, and needing to be cascaded manually.

To see where this has happened, we can dive into the ReportServer database to check where the inheritance is broken:

SELECT
	ObjectType = CASE c.[Type]
		WHEN 1 THEN 'Folder'
		WHEN 2 THEN 'Report'
		WHEN 3 THEN 'File'
		WHEN 4 THEN 'Linked Report'
		WHEN 5 THEN 'Data Source'
		ELSE 'Etc.' END,
	c.[Path]
FROM dbo.[Catalog] c
WHERE c.PolicyRoot = 1
	AND c.[Path] <> ''
ORDER BY c.[Path];

The PolicyRoot field indicates that permissions are assigned to this item explicitly and can be used as the root for child items which inherit permissions (PolicyRoot of zero). This query therefore shows where inheritance has been broken. It doesn’t mean permissions have changed, but it’s somewhere to review if the break is the correct choice. Financial or personnel data may be good reasons for this.

When reviewing security settings for an object, if inheritance is broken, you’ll see options including to revert the security:

If the object has inherited security, you’ll see the following:

Within the database we can also use the PolicyUserRole table to check permissions granted to specific accounts. If you want to see where inheritance has been broken and permissions differ between parent and child level, try this out:

WITH Removed AS (
 
	SELECT
		[Type] = 'Removed',
		c.ItemID,
		pur.ID
	FROM
		dbo.[Catalog] c
		INNER JOIN dbo.[Catalog] cp ON c.ParentID = cp.ItemID
		INNER JOIN PolicyUserRole pur ON cp.PolicyID = pur.PolicyID
		LEFT JOIN PolicyUserRole ur
			ON c.PolicyID = ur.PolicyID
			AND pur.UserID = ur.UserID
			AND pur.RoleID = ur.RoleID
	WHERE
		c.PolicyRoot = 1			/* Not inheriting the policy */
		AND ur.ID IS NULL			/* User and role is not present from the parent */
 
), Added AS (
 
	SELECT
		[Type] = 'Added',
		c.ItemID,
		ur.ID
	FROM
		dbo.[Catalog] c
		INNER JOIN PolicyUserRole ur ON c.PolicyID = ur.PolicyID
		INNER JOIN dbo.[Catalog] cp ON c.ParentID = cp.ItemID
		LEFT JOIN PolicyUserRole pur
			ON cp.PolicyID = pur.PolicyID
			AND ur.UserID = pur.UserID
			AND ur.RoleID = pur.RoleID
	WHERE
		c.PolicyRoot = 1			/* Not inheriting the policy */
		AND pur.ID IS NULL			/* User and role is not present at the parent */
)
SELECT
	ObjectType = CASE c.[Type]
		WHEN 1 THEN 'Folder'
		WHEN 2 THEN 'Report'
		WHEN 3 THEN 'File'
		WHEN 4 THEN 'Linked Report'
		WHEN 5 THEN 'Data Source'
		WHEN 8 THEN 'Data Set'
		ELSE CAST(c.[Type] AS VARCHAR) END,
	c.[Path],
	u.UserName,
	r.RoleName,
	x.[Type]
FROM
	(SELECT * FROM Added
		UNION ALL
		SELECT * FROM Removed ) x
	INNER JOIN dbo.[Catalog] c ON x.ItemID = c.ItemID
	INNER JOIN dbo.PolicyUserRole pur ON x.ID = pur.ID
	INNER JOIN dbo.Users u ON pur.UserID = u.UserID
	INNER JOIN dbo.Roles r ON pur.RoleID = r.RoleID
ORDER BY
	c.[Path],
	u.UserName,
	r.RoleName;

In the above query, the Added and Removed CTEs simply join the policy details between parent and child objects and identify where the user + role combination isn’t present in either direction. This shows where permissions have diverged after the inheritance has been broken.

Armed with those details you can jump into the portal to investigate and align to your policies.

With SSRS security, its easy for permissions to drift over time – customised and forgotten about as new accounts are added at a higher level. Defining a clear security policy and applying it consistently can be a light touch to bring some order to the chaos.