Re: Application Role VB6

From: Paul Whittaker (PaulWhittaker_at_discussions.microsoft.com)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 07:19:03 -0800

Thanks Dan

That was spot on, it was opening another connection (a recordset hadn't been
closed before it tried to execute the stored procedure).

Paul

"Dan Guzman" wrote:

> You might try running a Profiler trace to see what's going on behind the
> scenes. I suspect ADO is opening a separate connection for your stored proc
> execution and this fails because the app role wasn't activated on that
> connection.
>
> Note that ADO will implicitly open another connection when the specified
> connection is busy, such as in the case of an open recordset on that
> connection.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Paul Whittaker" <PaulWhittaker@discussions.microsoft.com> wrote in message
> news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@microsoft.com...
> > In VB6 I have the following code to use an application role
> > With cn
> > .ConnectionString = "MyConnectionString"
> > .ConnectionTimeout = 10
> > .Properties("OLE DB SERVICES") = -2
> > .Open
> > 'Had to allow execute for sps against the windows group until we
> > can
> > suss app role
> > .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N
> > 'MyPassword'},'ODBC'"
> > End With
> >
> > I added a windows group to my sql server database, containing windows
> > users
> > who will use this app. I gave the group no permissions of any kind. This
> > group as I understand it will allow the users to open the initial
> > connection
> > to the database before the application role is then applied to the
> > connection.
> >
> > Next I added an application role to the database and gave it select,
> > insert
> > etc. on any tables the app needs and execute permission on any stored
> > procedures the app uses.
> >
> > When a user (from the windows group) runs the app, data can be selected
> > from
> > the tables but you get 'Execute permission denied' on any stored procedure
> > that runs.
> >
> > If I explicitly grant permission to the windows group for one of the
> > stored
> > procedures, they are able to access the stored procedure from within the
> > app.
> > Ok, I think it looks like the app isn't using the application role.
> >
> > If I delete the application role from the database and amend the VB code
> > so
> > as not to use an application role, the user is unable to perform the
> > select
> > operation on any table, this confuses me as it looks like it was using the
> > application role to gain access to the data.
> >
> > Anybody got any ideas.
> > Regards
> > Paul
>
>
>

Relevant Pages

  • Re: ARITHABORT problem
    ... Anyway, when you open the AdoConnection, execute the following: ... Connection:= ... > that inserts new rows into the table, I get an ARITHABORT error. ... > the same stored procedure from the MS SQL QA it works fine. ...
    (borland.public.delphi.database.ado)
  • Re: ARITHABORT problem
    ... Anyway, when you open the AdoConnection, execute the following: ... Connection:= ... > that inserts new rows into the table, I get an ARITHABORT error. ... > the same stored procedure from the MS SQL QA it works fine. ...
    (borland.public.delphi.database.ado)
  • Re: debug mode faster than binexe ?
    ... the bin directory. ... If that is the case then you are running into connection caching in SQL ... The first time you execute in VS it will create a new connection. ... There is a possiblity after that the app (since it is running as a process ...
    (microsoft.public.dotnet.languages.vb)
  • Re: A month with a Nokia n95 (long)
    ... and can't play. ... One of these days I will get around to writing a small app to do it all on the fly. ... HSDPA works well and you can have more than one internet connection going at once. ... For example I can use it as a modem for my laptop and also "listen again" to a Beeb radio programme. ...
    (uk.telecom.mobile)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... It looks like it is not going to be an easy job getting it to work on the SQL Server 2005 and Express:(. ... 'Then you can execute the command and then retrieve the ID ... I am 100% sure this code works against any version of SQL Server, as long as the connection is OK and the SP has two parameters @varCompany as Input and @Return_Value as Output. ... 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. ...
    (microsoft.public.dotnet.framework.aspnet)