I have a whole list of reports that I've been asked to publish to a wider audience.
We don't want to add these users (windows authentication) to the list of database users so I have set the reports datasource to use a specific username/password.
This account has all the necessary permissions in the database. All worked fine for me.
Then I deployed the new datasource and asked a user to test. They get connection errors.
I set up a profiler trace to see what was happening at the database level.
I open the report, select parameters and view the report.
The parameters are based on a query - one drop down box populates a second based on user selection.
Although the report itself is being run under the security context of the specific account the query that populates the second drop down box is run under the logged on users (windows) account.