Application Role

Hi,
I have an Access 2003 project which uses a "dummy" login for the standard
connection without any rights on objects. After establishing the ADO
connection via VBA code I enable the application role which is then used to
do all database access
cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
This works fine unless I assign a rowsource via a code line like this
...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
This works only when I assign the dummy login the right to execute the
stored procedure. It is not enough to assign this right to the Application
role. But this means that the dummy role has access to this table via the
stored procedure which I wanted to avoid. What do I miss, or is this by
design? Any other solutions for this scenario?
TIA,
Norbert MeissNorbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> I have an Access 2003 project which uses a "dummy" login for the
> standard connection without any rights on objects. After establishing
> the ADO connection via VBA code I enable the application role which is
> then used to do all database access
> cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
> This works fine unless I assign a rowsource via a code line like this
> ...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
> This works only when I assign the dummy login the right to execute the
> stored procedure. It is not enough to assign this right to the Application
> role. But this means that the dummy role has access to this table via the
> stored procedure which I wanted to avoid. What do I miss, or is this by
> design? Any other solutions for this scenario?
Since I don't know Access, I will have to guess a little here. But I have
a feeling that the RecordSource stored procedure gets executed on a second
connection to SQL Server. In such case, that connection will not have the
application role set.
Why you get this second connection, I don't know. It could be inherent
with the Form.RecordSource thing, and thus no way to avoid it. But there
is also an issue with ADO. If you fail to get all data from a query, some
client libraries will tell you that the connection is busy, and no new
query can be submitted. But ADO tries to be a nice guy, and opens a new
connection to SQL Server and submits the qurey on that connection. This
often causes more problem than it solves. Really to avoid this, I don't
know, since I know nothing about your code. But "SET NOCOUNT ON" saves
the day in many cases, since the result sets you have not consumed,
often are rowcounts from INSERT/UPDATE/DELETE statements.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I cannot reproduce the strange behaviour from my first post - maybe I was
wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
didn't understand its meaning so far. Thank you.
Norbert Meiss
"Erland Sommarskog" wrote:

> Norbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> Since I don't know Access, I will have to guess a little here. But I have
> a feeling that the RecordSource stored procedure gets executed on a second
> connection to SQL Server. In such case, that connection will not have the
> application role set.
> Why you get this second connection, I don't know. It could be inherent
> with the Form.RecordSource thing, and thus no way to avoid it. But there
> is also an issue with ADO. If you fail to get all data from a query, some
> client libraries will tell you that the connection is busy, and no new
> query can be submitted. But ADO tries to be a nice guy, and opens a new
> connection to SQL Server and submits the qurey on that connection. This
> often causes more problem than it solves. Really to avoid this, I don't
> know, since I know nothing about your code. But "SET NOCOUNT ON" saves
> the day in many cases, since the result sets you have not consumed,
> often are rowcounts from INSERT/UPDATE/DELETE statements.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||In addition to what Erland said, you are going to have intermittent
problems using application roles with Access unless you turn off
connection pooling in the connection string and test to make sure that
the application role is active before you execute any EXEC statements.
This is all going to add overhead to the app and slow it down somewhat
-- how much depends on the app and number of users. It is generally
recommended NOT to use application roles with Access because it opens
and closes connections under the covers. See
http://support.microsoft.com/defaul...B;EN-US;Q229564 for
more info on the connection pooling issue.
--Mary
On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I cannot reproduce the strange behaviour from my first post - maybe I was
>wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
>didn't understand its meaning so far. Thank you.
>Norbert Meiss
>"Erland Sommarskog" wrote:
>|||Thanks Mary.
While performance is not an issue so far with some 25 users and a big server
I will give it a second thougt and some testing.
Norbert
"Mary Chipman" wrote:

> In addition to what Erland said, you are going to have intermittent
> problems using application roles with Access unless you turn off
> connection pooling in the connection string and test to make sure that
> the application role is active before you execute any EXEC statements.
> This is all going to add overhead to the app and slow it down somewhat
> -- how much depends on the app and number of users. It is generally
> recommended NOT to use application roles with Access because it opens
> and closes connections under the covers. See
> http://support.microsoft.com/defaul...B;EN-US;Q229564 for
> more info on the connection pooling issue.
> --Mary
> On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
> <NorbertMeiss@.discussions.microsoft.com> wrote:
>
>|||A good tool to use when testing is Profiler, which allows you to
eavesdrop on the actual conversation Access is having with the SQL
Server. It's interesting to see what's going on under the covers when
it comes to troubleshooting existing problems and avoiding potential
ones.
--Mary
On Mon, 27 Dec 2004 06:39:04 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Mary.
>While performance is not an issue so far with some 25 users and a big serve
r
>I will give it a second thougt and some testing.
>Norbert
>"Mary Chipman" wrote:
>