I've got a report that runs perfectly well for me and not for non db admin users.
The users can run reports from the same project, using the same shared datasource, accessing the same database, and as far as I can see the permissions for all of the database tables and stored procedures are exactly the same. The users in question have the same rights to all of the tables in the database in question (dbo).
The report has 2 datasources, one for a parameter and one for the final results. The log files indicate that the select query won't execute on the table that gets data for the parameter and this is due to a permissions error.
Has anyone got any clue where I should look further for this?
Make sure that the user has appropriate permission for the table/function/view/stored procedure at the database level. so you may want to grant execute permission for the group where these users are belong/located.
Think out of the box
Thanks for the answer, however they already do have the required permissions. There are other reports using stored procedures and tables in the same database that they can run, and they have exactly the same permissions for those, which is why I'm confused.
Any other ideas?
Couple other things that you can check. If you have access to report server, look up the properties of the shared datasource that the report is using, and see how it being connected by. Normally, you would use Windows Integrated Security, but see what it's currently being use. Another thing you want to see if the security properties of that report in report server. I'm not sure how you give access to your user, but make sure that the user or the group that the user belongs to has appropriate at least browser role. good luck
Think out of the box
Hi there, I've checked all of the report server datasource properties and the user already accesses other reports that use the same datasource, so permissions there are fine. I've also ensured that the user has rights to the report itself and the tables and stored procedures.
I've tried to narrow it down, and think I may have the cause, although I don't know why and how to resolve it!
The report has some parameters and the parameter values are selected by the user from a dropdown list, the values of which are generated by another stored procedure accessing the same datasource. If I remove the parameters, the users can run the report fine, but as soon as I add them back in the problem occurs.
Could it be something to do with the user trying to connect to the datasource twice? I've done this in other reports and not had this problem, so I can't understand why it would cause a problem but it's the only think I can think of...
Have you got any other ideas?