Non-Admin users can view but not run reports

Question

I have been all over the internet for 3 days trying to determine why non-admin users cannot get access to reports as part of the 'Browser' role.

I am in sql server 2008 R2 on a Windows 2003 server.  I can successfully connect to the report server when logged in as an admin user or my non-admin ReportViewer user.  As the admin user I can see all reports & datasets.  As the non-admin I can see the reports but not the datasets - which is what I want.  But, when I try to trun the report I get the error:

The permissions granted to user are insufficient for performing this operation. (rsAccessDenied)

Now, I can solve this by making he user an admin on the server - but then they also see the datasets - can't allow that. 

In sql server my <domain>\ReportViewer Login belongs to the sysadmin server role and each database has a user tied to this Login of type db_datareader.  I can log in via SSMS as the ReportViewer user and I can successfully execute all report queries.

Any thoughts?

---

Pat Reddy

Answer 1

Hello,

try to check those steps:

1. DataSource have set "<label for="ui_rdoStored">Credentials stored securely in the report server</label>" and you use ReportViewer login with appropriate password

2. Users have browser role on whole folder

3. Report does not have special security setting (all report security setting is inherited from folder)

For hide datasources and datasets just click on "Hide in tile view" in folder (or DS) property page.

Users want's rights for dataset (especially if they are getting data from DB with their own credentials). You only can hide it.

Zdenek

---

Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

Answer 2

Thanks for the reply - I'll try this tomorrow.  I suspect your parting comment says it best - "Users want's rights for dataset (especially if they are getting data from DB with their own credentials). You only can hide it."  I am indeed attempting to use Windows credentials of user when pulling reports.  Perhaps that's the culprit.  I can embed Sql Server user instead - or just hide the datasources, like you suggested.

---

Pat Reddy

Answer 3

Don't know how Zdenek's  post was determined to be the "answer" without my feedback, but I guess it's the only I received so...whatever.

Anyway, I have still not solved the problem but have used the suggested work around.  It is far far from an ideal solution and I can't believe that what I implemented is what Microsoft intended.  I have a single user whose only function is to to able to view reports and I had to make them an administrator on the server.  That's insane.  Yet, nowhere does anyone provide documentation (that I can find - clearly) on how to set up a user for report access only - yet it is what 99.99999% of my users are - at this client and many others.

---

Pat Reddy