Re: Application Role

From: Norbert Meiss (NorbertMeiss_at_discussions.microsoft.com)
Date: 12/27/04 

Date: Mon, 27 Dec 2004 00:19:03 -0800

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
>

Relevant Pages

  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... table, if another inserting occurs at the nearly exactly the same moment, ... SQL Server will pass it a default value. ... assume that you do have a connection that can reach SQL Server Express. ... unless your stored procedure has some thing that only works ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... Of course this is assume that you do have a connection that can reach SQL Server Express. ... I do not think your problem is due to difference of SQL Server2000 and SQL Server2005, unless your stored procedure has some thing that only works in SQL Server2000, not SQL Server 2005. ... return @RETURNVALUE or the SQL Server 2000 version won't work either. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... set cmd = Server.CreateObject ... 2000, 2005), as long as the connection is OK and the SP has two parameters ... If the problem is not with the SQL server Exp, then how come the SQL Sever ... unless your stored procedure has some thing that only ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... The connection string is fine as I could make connection to the DB and create the new table. ... return @RETURNVALUE or the SQL Server 2000 version won't work either. ... Then, check the stored procedure and make sure it is using RETURN @value, or similar. ... is using the SQL Server 2000 and I am testing to use it on the Express ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ANSI_WARNINGS error in Stored Procedure
    ... The error says to set these options for your _connection_ not for the query. ... > I have a linked Server on my regular SQL server, and when I try to create ... a stored procedure that reads from the linked server, ...
    (microsoft.public.sqlserver.security)