Issues with Reporting Services Permissions

After working with TFS security for the better part of a year, I suddenly ran into a nasty permissions issue in Reporting Services this week.  I'll illustrate the problem using pictures.  When I look at security at the top level in Reporting Services using my tfsSetup account, I see that only the TFS Administrators group has the Content Manager role.  That's good because I don't necessarily want everyone looking at the project reports across the board.

SSRS Top-level Permissions

Now I'll drill down into the folder for my team project.  In this case, the team project name is JobBoard.  Here I've broken the security inheritance and given my jobBoardProjectAdmin account the Content Manager role.  The expectation is that he or she will be able to manage security for that folder and any of the contained items without having to bug me.  I'd like to fully delegate that function to the JobBoard Team Project Administrator.

Project Folder Permissions

With that set up, let's see if the jobBoardProjectAdmin account has access to view the reports as well as administer security for the JobBoard folder.

Project Admin tries to access project folder permissions2008-12-05_1323

Looks like I can see the list of reports but then I click the Properties tab...uh oh!  That doesn't look right.  Reporting Services gives me an error stating "The permissions granted to user 'VSTS2008\jobBoardProjectAdmin' are insufficient for performing this operation. (rsAccessDenied)".  I just gave that account the Content Manager role!  I'll check one of the reports.  Maybe that will work.

2008-12-05_13262008-12-05_1327

Same error.  I can view the report just fine, but I can't edit the permissions.  Argh!

So I do some Googling and really don't find much except for folks talking about giving the user some sort of access at a higher level.  I'd rather not have to do that but if I'm going, I'd prefer to do it once and get it over with.  I'll try giving all Authenticated Users the Browser role at the top level.  This may seem to fly in the face of my earlier statement about not wanting everyone to see all of the projects reports.  However, since I broke the security inheritance for the JobBoard folder, the Authenticated Users group won't have the Browser role at that level.  Only the users explicitly given permissions to the folder will be able to see it.  I'll use that policy going forward so the Authenticated Users approach is okay.

2008-12-05_1324

Now I'll I try the Properties tab for the JobBoard folder using the jobBoardProjectAdmin account.  The result:  I can see it AND manage permissions.

2008-12-05_1328

Success!...kind of.  Why does this work?  Why does this fix the problem?  Why do I have to do this?  Is this the way it's supposed to work?  I really don't know, but I'll have to live with it for now.  I'm working with SQL Server 2008 here.  The problem can be seen in SQL Server 2005, as well.  If anyone has feedback or ideas about this, feel free to respond.  If you're looking for a solution to the problem in your own environment, the Authenticated Users should work just fine.  You could always use a more specific domain group if you like.

On a side note, during this process I discovered a nice little feature in Visual Studio Team System that I hadn't seen before.  In the Security and Group Membership dialogs that are part of Team Explorer there are these links for SQL Server Reporting Services and Windows SharePoint Services Site Administration at the bottom.  I never paid attention to those until now.  They actually take you straight to the appropriate locations for administering security for those resources.

2008-12-05_1235

Interestingly enough, the Reporting Services link takes to you to the Properties tab of the top-level folder rather than the folder for the team project.  Strange...

jb

1 comment:

Bill said...

I was having this EXACT same problem and this fixed it. It makes no sense why we should have to do this, but I'm glad I found the solution. Thanks!