Application Role with VB6

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 t
o
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 t
o
the windows group this somehow overrode the application role. This is clearl
y
rubbish, because as I understand it, once an application role takes over, no
other permissions matter.
Anybody see what I'm doing wrong?
Thanks
PaulPlease 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 c
an
> 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 user
s
> 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 store
d
> procedures
> When the application runs under a windows user (who is in the windows grou
p
> 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). I
f 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 delet
ed
> the application role from the database and amended the VB code, the user i
s
> 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 clea
rly
> 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

Application Role VB6

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 connectio
n.
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
PaulYou 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|||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 pr
oc
> 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 messag
e
> news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
>
>