Application Role with VB6

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

Date: Mon, 24 Jan 2005 05:47:04 -0800

In VB6 I've got the following code when opening a connection to sql server:
    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

In sql server I've added a windows group to the database to allow the users
of the application to open the connection. I gave the windows group no
permissions of any kind. I added an application role and gave it select,
insert etc. permissions on the tables and execute permissions on all stored
procedures

When the application runs under a windows user (who is in the windows group
I added to the databse), the application can select data from the database
but gets 'execute permission denied' on any stored procedure that it tries to
run.

If I examine the permissions of one of these stored procedures, there is a
grant on it for the application role (there are no denies on it at all). If I
then explicitly grant permission on one of these stored procedures to the
windows group containing the windows user, they are able to access it.

Ok, I think, it looks like it is ignoring the application role, so I deleted
the application role from the database and amended the VB code, the user is
then unable to access any data.

My conclusion then becomes the application role was giving the user access
to table data, but not to stored procedures, and when I granted permission to
the windows group this somehow overrode the application role. This is clearly
rubbish, because as I understand it, once an application role takes over, no
other permissions matter.

Anybody see what I'm doing wrong?
Thanks
Paul

Relevant Pages

  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005
    ... access for the Visual Studio 2005, when working on ADO.NET (SQL Server ... Permissions are per logins and users. ... permissions can be granted through stored procedures, ...
    (comp.databases.ms-sqlserver)
  • Re: Securing Stored Procedures from being seen
    ... PERMISSIONS works for the current user, whoever he may be, and however his rights were granted. ... username, but in SQL Server, there are too many users to map an individuals ... granting the group execute permissions. ... see the names of any stored procedures to which he has no rights. ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... properties of the sp_OACreate extended stored procedure has a permissions ... With SQL Server 2000 Enterprise ... Apparently only members of the sysadmin role can ... these stored procedures, however, the sql login for this application is ...
    (microsoft.public.sqlserver.security)
  • Re: Check SQL Server Table Permissions
    ... "BillyRogers" wrote in message ... program calls stored procedures in SQL Server from Access. ... permissions having been dropped for the group from one of the tables. ...
    (microsoft.public.access.modulesdaovba)