Microsoft Access Project (ADP) - Report Recordsource Permission Error

[milamj]

Hi folks,

My environment:
Front end: Access 2007 ADP, Back end: SQL Server 2005, Security: Windows login, I created Schemas and roles to control access, and I only expose data through views (with metadata option, to encapsulate the tables).

My problem:
I have a report based off a form. They are basically identical (same fields, same recordsources, etc.) Both have two levels of subforms/reports. The form works perfectly, users can insert, update and delete records. The report works fine (opened via VBA form button click event) for any user with db_datareader permission, but won't open for anyone without it. Gives an error.

The error msg:
"The record source '<<My View>>' specified on this form or report does not exist"

I have qualified my views with the appropriate Schema, e.g., Recordsource: MySchema.MyView, Recordsource Qualifier: MySchema

I've seen several posts regarding this error when the record source is a sproc, but in my case it's a view, and the users have permissions on that particular schema. Obviously this is a permissions issue, but I'm at a loss. As a workaround, I tried to copy the recordsets from the form & subforms to the reports but the middle tier subreport is giving me an error. Sigh...

Can anyone steer me in the right direction? Thanks in advance!!

 

[lameid]

Have you granted select permission to users/groups that you want to be able to run the report?

 

[milamj]

Thanks for responding. I got pulled off on something else and haven't come back to this yet.

Yes... users can access (& even edit) data from the very same views via a form. That's why I'm stumped. Don't know why the report would behave differently than the form when they use the exact same recordsource? Any advice would be useful.

(I'm waiting for trace permissions on the SQL Server so I can troubleshoot that way. Wondering if reports read some kind of system table?).