ADP - Report Recordsource Permission Error - View ... Does Not Exist

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

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. The report & form 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 the error:

"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: MySchemaI'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!!
 
m:
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 the
error:

"The record source '<<My View>>' specified on this form or report
does not exist"
Click to expand...

Is the answer not obvious? Everybody who needs to run the report
needs to have db_datareader permissions (or the equivalent defined
in some other fashion). Users can't run a report on data they don't
have permission to see!
 
Is the answer not obvious? Everybody who needs to run the report
needs to have db_datareader permissions (or the equivalent defined
in some other fashion). Users can't run a report on data they don't
have permission to see!
Click to expand...



Thanks for responding. I'm apparently missing something. The users
already have permission on the views. They can access them just fine
through the form, but not through the report. I don't understand why
one would work and not the other. Forgive my ignorance, this is my
first time working with ADP.

(Also, the reason I don't give db_datareader permissions to the users
is because I am restricting data with rowlevel permissions via the
view. Users should only see their own data, and are restricted from
seeing some tables altogether).

Thanks for your help.
 
m:
I'm apparently missing something. The users
already have permission on the views. They can access them just
fine through the form, but not through the report.
Click to expand...

Using the same view or a different one?
 
Using the same view or a different one?
Click to expand...


Same view.

I did some testing and found that none of my reports work unless I
give db_datareader permissions to the users, even when I grant select
permissions on the base tables. Whereas all the forms using views
work just fine. I only just started building reports, so just
discovered this problem. Another weird thing is the users can't see
the views in the database window query pane, yet the forms using those
views work fine. :/

I'm auditing the permissions in the backend now. And as soon as the
server admin gives me access, I'll run a trace to see if reports are
hitting some system table or something. Again, I'm new to ADP. If
you have any suggestions, I'd be very appreciative. Thanks.
 
m:
I did some testing and found that none of my reports work unless I
give db_datareader permissions to the users, even when I grant
select permissions on the base tables. Whereas all the forms
using views work just fine. I only just started building reports,
so just discovered this problem. Another weird thing is the users
can't see the views in the database window query pane, yet the
forms using those views work fine. :/
Click to expand...

Maybe this is an aspect of the ADO problem, where ADO ignores your
view and goes directly to the underlying tables. I have only heard
about that being a problem with DML queries (not just SELECTs), but
it could have some interaction with security as well.
I'm auditing the permissions in the backend now. And as soon as
the server admin gives me access, I'll run a trace to see if
reports are hitting some system table or something. Again, I'm
new to ADP. If you have any suggestions, I'd be very
appreciative.
Click to expand...

I wonder if there are any special properties in the report that
might have an effect on this? Since I don't use ADPs, I have no
suggestions, but you might have a look at the DATA and OTHER
properties of your report.
 
Maybe this is an aspect of the ADO problem, where ADO ignores your
view and goes directly to the underlying tables. I have only heard
about that being a problem with DML queries (not just SELECTs), but
it could have some interaction with security as well.


I wonder if there are any special properties in the report that
might have an effect on this? Since I don't use ADPs, I have no
suggestions, but you might have a look at the DATA and OTHER
properties of your report.
Click to expand...


Thanks for the feedback... I'll look into those properties as you
suggest. If I figure out what's wrong I'll post my solution here.
 
Back
Top