SSRS 2008 R2 + Permissions granted to user are insufficient for performing this operation.(rsAccess Denied)

ssrs-2008

I spent hours to figure this one out and now I gave in.

I hope someone can help me with.

SSRS 2008 R2 report server is up and running and Using BIDS, I designed a sample report and deployed successfully.

As a admin, I can go and see everything and run the sample reports.

What I am trying to achieve here is to limit users' access to see folders and reports.

This is what I have done to achieve this:

  1. Created few groups in the local server (where the SQL server is installed)

    SQLReportUsers - Users who has access to SQL Server Reporting Services (Web) 
Finance - Users who can access to Finance folder in Home Folder
SalesTeam - Users who can access to SalesTeam Folder in Home folder

  2. I created users and assigned them to one of finance/Sales Team group and they all belong to SQLReportUsers group.

  3. Reason for having SQLReportUsers group is to even limit any random users to access to the reporting services. Creating 'Everyone' will allow to access to it (even though they won't be able to see anything without rights to access to items (folders, reports). In Home foder, I SQLReportUsers as Browser.

  4. Samples reports deployed to Finance folder and SalesTeam Folder. Data Sources and DataSets are in Home folder and they are not visible to others users (I removed)

  5. I created SQLReport user account in SQL Server which I want to use it as a central point to access to data in SQL. then Under Properties of Data source (only one now), I used 'Credentials sotred securely in the report server' and supplied User Name and Password. Test connection is ok.

  6. I created a test user (of Windows server 2003) then assigned to SQLReportUsers and Finance.

  7. Opened the Internet explorer and logged into the reporting services successfully.
    as expected, only finance folder is visible to this user account. and inside the finance folder, there is the sample report.

The problem is that when I click the report, it says that

'An error has occurred during report processing. (rsProcessingAborted)
The permissions granted to user 'MSERVER-DELL\test2' are insufficient for performing this operation. (rsAccessDenied)'

Everything works fine but the user can not open the report.

I tried all the options under 'Credentials stored securely in the report server'

I tried 'Use as windows credentials when connecting to the data source' (not just report account but also my admin account') as well as 'Impersonate the authenticated user after a connection has been made to the data source.

All didn't work. I tried all the possible combination but it didn't work.

Under processing options of the report item, I did 'Cache a temporary copy of the report. Expire copy of report after a number of minutes: 30' under my admin access then back to the user account, user can pull the report.

this implies me that it's the datasource that the user account is having problem with.

I wonder where it went wrong.

Could you help me out please?

thank!

Best Answer

I had the exact same problem as this and a solution that was almost exactly what Ken Mc said. The report I was trying to open contained both a Shared Data Source as well as a Shared Dataset. I had given authenticated users access to browsing the data sources, but completely forgot about the data sets and the cryptic error message never led me to that issue.

So in summary, if you are using any external(shared) data sources or datasets, make sure to enable the correct security role for your uses on that folder.

Related Topic