Re: Application Role
From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 12/27/04
- Next message: Sophie Guo: "Re: Firewall ports to open"
- Previous message: Norbert Meiss: "Application Role"
- In reply to: Norbert Meiss: "Application Role"
- Next in thread: Norbert Meiss: "Re: Application Role"
- Reply: Norbert Meiss: "Re: Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Sun, 26 Dec 2004 23:43:50 +0000 (UTC)
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: Sophie Guo: "Re: Firewall ports to open"
- Previous message: Norbert Meiss: "Application Role"
- In reply to: Norbert Meiss: "Application Role"
- Next in thread: Norbert Meiss: "Re: Application Role"
- Reply: Norbert Meiss: "Re: Application Role"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
