Update SSRS security for reports

Question

I am looking to update the SSRS security on reports. I have been asked to tidy up the reports . Is there a query that can do this for me on sql server?

Answer 1

Here is how to update the security settings:

With a query

Note: This is not supported

First, you have to understand the following concepts:

• The Catalog table contains the Reporting Services items (reports, folders, ...)
• The security information (called Policy), is located in the PolicyID column of the Catalog table
• The user rights are called Roles and are stored in the Role table
• The users are located in ... wait for it ... the Users table
• The PolicyUserRole table links a User, a Role and a Policy

And last but not least, by default an item inherits the security settings from the parent folder. If you change it, a new Policy will be created.

What you want to do is locate the security setting GUID you want to use:

SELECT * FROM [Catalog] WHERE [Path] = '/NewFolder'

Then, apply it to all the elements you need. Here is an example for the children items:

UPDATE [Catalog] SET [PolicyID] = '<NewFolderPolicyID>' WHERE [Path] LIKE '/NewFolder/%'

Manually

Note: the following applies if you don't have a lot of items or if you have plenty of time to spend.

Change the Parent folder security settings. As stated above, all the elements in the folder where the inheritance has not be broken will automatically use the parent settings.

Then, browse reports and choose "Revert to Parent Security" in the security options for reports having settings different than the parent report.