Friday, December 11

Which Reports Aren't Inherting Permissions?

Managing security settings in Reporting Services through the Report Manager website is usually pretty easy. However I've found that sometimes things go astray. Today I found a few reports which weren't inheriting permissions from their parent directory. Easy enough to fix, you jump into the security settings for that report and set "Revert to Parent Security". However, I have dozens of reports in each of a dozen directories; if I suspect this problem is going on in other reports, I'm not keen on a "needle in a haystack" search.

Here's the code for identifying which reports are no longer inheriting permissions from their parent:

USE ReportServer
SELECT
        
[Path],
        
[Name]
  
FROM
        
[dbo].[Catalog]
  
WHERE
        
[PolicyRoot] = 1

8 comments:

mkokoy said...

thanks for that info, do you know if there is away with script to revert that permissions and make the inherit for all the folders and subolders

Unknown said...

I guess you could write an update statement which alters the policyroot value, however I'm not too sure what that value should be.

Unknown said...

I wrote this blog post 7 years ago :)

Sarah G said...

And I still found it helpful today in 2017! :) A quick tweak, adding "and [Type] = 2" at the end will limit the query to just reports, if you don't want to include subfolders that aren't secured the same as their top level folders.

Unknown said...

Thanks Sarah!

Rudy said...

Been looking for this query for ages. SSRS could use more internal security reports, but this will have to do in the meantime.

xn0mad said...

just posting here to say that this saved our butts today.

Kirja said...

Still helpfull in 2019 too :D
Thnx