Application Role VB6

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


Date: Mon, 24 Jan 2005 05:59:03 -0800

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: Application Role VB6
    ... I suspect ADO is opening a separate connection for your stored proc ... > suss app role ... > When a user (from the windows group) runs the app, ... > the tables but you get 'Execute permission denied' on any stored procedure ...
    (microsoft.public.sqlserver.security)
  • Re: Just for kicks, try this
    ... Robbe Morris - 2004-2006 Microsoft MVP C# ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MSDE Slow in executing Stored Procedures
    ... App that creates an ADO connection object and execute a simple ... stored procedure and its equivalent insert statement. ... MSDE 7.0) vs. SQL Server 2000. ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: Just for kicks, try this
    ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... app using ado.net execution time of some stored procedures has increased ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Binding Access Forms to a SQL Server Data source : View Or StoredP
    ... I use a similar scenario for our app. ... My form source is a Stored Procedure that returns only ... My custom Navigation buttons change the inputparameters of the ... Stored procedure that any of my main forms can call with the table name to ...
    (microsoft.public.access.adp.sqlserver)