Re: Application Role
From: Norbert Meiss (NorbertMeiss_at_discussions.microsoft.com)
Date: 12/27/04
- Previous message: Mary Chipman: "Re: Application Role"
- In reply to: Mary Chipman: "Re: Application Role"
- Next in thread: Mary Chipman: "Re: Application Role"
- Reply: Mary Chipman: "Re: Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 27 Dec 2004 06:39:04 -0800
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/default.aspx?scid=KB;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:
>
> >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:
> >> > 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/techinfo/productdoc/2000/books.asp
> >>
>
>
- Previous message: Mary Chipman: "Re: Application Role"
- In reply to: Mary Chipman: "Re: Application Role"
- Next in thread: Mary Chipman: "Re: Application Role"
- Reply: Mary Chipman: "Re: Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
