Thursday, October 06, 2005

SQL Server 2000 Reporting Services Security Issue/Workaround

One of my current projects uses SQL Server 2000 Reporting Services to provide rich reporting via a web interface that is linked to from a OSASPADO web app. Most reports are public reports, but there are a handful that are restricted to only a few people.

To simplify this partitioning of the report collection, I created a subfolder in the Report Manager, and we assigned different security to this subfolder. I also wanted one of the web application's "superusers" to be able to manage the list of regular users who had Browser access to the restricted subfolder.

So, I added a New Role Assignment to the subfolder's security list, specified the superuser's Domain User ID, and selected the "Content Manager" role. This should have given that user administrative-type access to just that subfolder.

However, every time that the user tried to access the Properties tab in the Report Manager, they would get a "rsAccessDenied" error.

So, I tried also making that user a Content Manager of the parent folder. Still no good.

It wasn't until I made them a Content Manager all the way up the chain to the root were they able to finally access the Properties tab of the restricted subfolder. We're going to live with this for now, but I'll be looking for a way to make my original plan work (that is, making that user a Content Manager of only the restricted subfolder).

Disclaimer: This particular organization currently only has RS SP1 installed, so it's quite possible that this issue was resolved in SP2, but I have no way at the moment to verify.