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!!

Jennifer <(E-Mail Removed)> wrote in
news:4ab0b246-c563-467f-a0c8-(E-Mail Removed)
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"

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!

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On May 7, 12:48*pm, "David-W-Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> Jennifer <jenn.mi...@gmail.com> wrote innews:4ab0b246-c563-467f-a0c8-(E-Mail Removed)
> 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"
>
> 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!
>
> --
> David W. Fenton * * * * * * * * *http://www.dfenton.com/
> contact via website only * *http://www.dfenton.com/DFA/



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.

Jennifer <(E-Mail Removed)> wrote in
news:69bb90b1-3c79-4616-b043-(E-Mail Removed)
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.

Using the same view or a different one?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On May 11, 5:24*pm, "David-W-Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> Jennifer <jenn.mi...@gmail.com> wrote innews:69bb90b1-3c79-4616-b043-(E-Mail Removed)
> 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.
>
> Using the same view or a different one?
>
> --
> David W. Fenton * * * * * * * * *http://www.dfenton.com/
> contact via website only * *http://www.dfenton.com/DFA/


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.

Jennifer <(E-Mail Removed)> wrote in
news:c58f5eb3-ed69-467e-a7c2-(E-Mail Removed)
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. :/

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.

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.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On May 13, 12:49*pm, "David-W-Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> Jennifer <jenn.mi...@gmail.com> wrote innews:c58f5eb3-ed69-467e-a7c2-(E-Mail Removed)
> 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. *:/
>
> 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.
>
> 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.
>
> --
> David W. Fenton * * * * * * * * *http://www.dfenton.com/
> contact via website only * *http://www.dfenton.com/DFA/


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.