Re: Application Role VB6

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 08:11:54 -0600

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: 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: ADO Connection.Execute Method leaves Open SQL Process
    ... Have the App create a single ADODB.Connection object, ... requesting only one connection from a single ADO Connection ... Pool, thus if all requests are sequenced and not over-lapping ...
    (microsoft.public.vb.database.ado)
  • Re: ADO Connection.Execute Method leaves Open SQL Process
    ... your first paragraph where it talks about "a VB6 app". ... requesting only one connection from a single ADO Connection ... ' at this point a new SQL process HAS been created!! ...
    (microsoft.public.vb.database.ado)
  • Memory leak with ADO
    ... When the app runs through the loop 2k times about 16-30 megs ... procedure TForm1.SpeedButton1Click(Sender: TObject); ... Caption = 'Connection String' ... object Connection: TADOConnection ...
    (borland.public.delphi.database.ado)
  • Re: Application Role VB6
    ... it was opening another connection (a recordset hadn't been ... closed before it tried to execute the stored procedure). ... >> suss app role ...
    (microsoft.public.sqlserver.security)