Query execution failed for data set <name> For more information about this error navigate

I have created and deployed my first report. It renders fine for me and the other database admin. When others attempt to view it, we get the error

Query execution failed for data set 'periods'. (rsErrorExecutingCommand), For more information about this error navigate to the report server on the local server machine, or enable remote errors

Initially, We created a local group on the machine that hosts both the database and webserver and added the individuals to that group. Then, within SRS Report manager, we added that group to the Browswer role of the report.

The error message was slightly different, in that it couldn't even open the Datasource.

We then added an individual to the database as dbreader, and got the above message. It apprently is starting to render, and when it encounters the first query (dataset "periods", which populates a drop down list for a parameter), it chokes. BTW, the Periods dataset executes a stored procedure dbo.Period_List that has no parameters. It returns a list of reporting periods.

I could not figure out how to "enable remote errors" or find an error log on the server. The C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles Log files did not appear to record any errors.

Please advise!

Have you given execute permissions on the stored procedure? You can try troubleshooting with SQL Server Profiler (http://msdn2.microsoft.com/en-us/library/ms187929.aspx), but that might be like hitting a gnat with a sledge hammer.

Larry

|||

I want to add some information. We recently enabled remote errors, and found that EXECUTE permission on the stored procedure was denied.

Does this mean that we have to grant permissions on every stored procedure to User Groups?

I love SRS, but this is turning into an Admin nightmare.

|||

Ok, let's back up a second and take another run at this. As long as you don't require the data on your reports to change based on who is viewing them, use a shared DataSource with stored credentials (http://technet.microsoft.com/en-us/library/ms178308.aspx). That way, you admin a single user and everyone (including you during development) sees the same report.

To continue to use Windows Authentication, yes, you would need to grant permissions for every stored procedure that you want SSRS to use to any user that will run a report using that stored procedure.

Larry

|||

thank you!

Ok, the first option sounds like the way to go for the moment, but something you said intrigued me...

"As long as you don't require the data on your reports to change based on who is viewing them..."

Actually, we would like to restrict what the users see based upon who they are, and haven't figured out how to do that yet. For example, if a person is from business unit A, we would like them only to see their business units data. All business units are in the same table however, so we would have to use a parameter or a filter, based upon the longin User ID. Not sure how to do this, any ideas?

|||

Well, you could implement row level security to filter the results returned based on the user. Here is a tutorial from Books On Line (BOL) (http://msdn2.microsoft.com/en-us/library/ms365305.aspx) and here is a white paper discussing it (http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx). It is a bit complex to get setup, but the results are well worth the effort.

Larry