Re: Application Role
From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 12/27/04
- Next message: Connie: "Re: How to sync SQL passwords between multiple servers"
- Previous message: Norbert Meiss: "Re: Application Role"
- In reply to: Norbert Meiss: "Re: Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 27 Dec 2004 12:14:11 -0500
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:
>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
>> >>
>>
>>
- Next message: Connie: "Re: How to sync SQL passwords between multiple servers"
- Previous message: Norbert Meiss: "Re: Application Role"
- In reply to: Norbert Meiss: "Re: Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
