RE: Application Role with VB6

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

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

Please ignore - accidentally posted twice

"Paul Whittaker" wrote:

> 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: What server hardening are you doing these days?
    ... permissions on their data, and Microsoft encourages ISVs to minimize ... I've been able to discuss ACLs and other security issues in Windows with ... Control or DAC (which is what you're referring to by the "stupid ...
    (Focus-Microsoft)
  • Re: Unnown process... 5eplorer.exe
    ... do not remove the cause (a "super"-hidden .dll program) but only remove ... symptom files and registry settings. ... It has all permissions but 'copy' denied to everyone, ... then by using the Windows XP Recovery Console. ...
    (microsoft.public.win2000.general)
  • RE: dcom permissions and vista?
    ... user BLAH with Local Activation and Local Launch permissions. ... Windows Vista indeed do some changes in handling DCOM and you may need to ... Windows Vista introduces the notion of Mandatory Access Labels in security ... Microsoft Online Community Support ...
    (microsoft.public.vc.atl)
  • Re: Passwords on Folders
    ... domain computer [there is also a recovery agent for a domain]. ... > Windows under which those permissions were defined. ... use NTFS on your hard drives so you can then EFS ...
    (microsoft.public.win2000.security)
  • RE: SBS 2003 Outoging Fax Problem w/Error 32028 (Cannot send - fatal error)
    ... 1.Reduce the baud rate of the incoming fax modem and see how it goes. ... Click Permissions and verify that the user attempting to fax has at ... 3.If you have configured the fax client on the Windows XP computer ... On the "Additional Server Types" page, ...
    (microsoft.public.windows.server.sbs)