How to troubleshoot a SQL Server application role not working in VB.Net app

Question

This desktop app written in VB.Net within VStudio 2008 fails when one of our "power users" tries to run the program:

Select permission denied on object 'AcctLogon', database 'MDS', owner 'dbo'.

The author of the app is out for a few weeks and I am the "fixer" while she's out. Both of our logins for SQL have permissions for db_owner in the MDS database but our user does not. The user has a Windows user login for this instance of SQL. This login has permissions for the public role only in the MDS database.

Rather than just give the user's login the same db_owner permission, I thought I'd try to fix the program by establishing an application role for the desktop program. When I tested the code, it seemed to work via debugging closely but after deploying the new version it failed with the same "permission denied" msg for the poweruser.

Here is what I did in VB.Net (this sub called in the startup form load event):

Private Sub SetApplicationRole()
Try
    Dim SQLConn As New SqlClient.SqlConnection
    SQLConn.ConnectionString = GetConnStr
    SQLConn.Open()
    Dim SQLCmd As New SqlClient.SqlCommand
    SQLCmd.CommandText = "Execute sp_setapprole AccountMaintenanceUtility, 'mypasswrd' "
    SQLCmd.Connection = SQLConn      
    SQLCmd.ExecuteNonQuery()
Catch sex As SqlException
    MsgBox(sex.ToString())
End Try
End Sub

'GetConnStr' is a property string that is defined in Settings.Settings as a Connection string with a scope of "Application" and its value is:

Data Source=MDWDATA;Initial Catalog=MDS;Integrated Security=True

In SQL, I ran the following script:

use MDS
go
sp_addapprole 'AccountMaintenanceUtility', 'mypasswrd'
go
sp_addrolemember 'db_owner', 'AccountMaintenanceUtility'

I cannot see what I've done incorrectly or overlooked. I hope to fix this at a sort of "high level" without delving deeply into her program and I cannot debug with Visual Studio on the desktop of the failing user.

E D I T - clarification:

How "persistant" is the setting of an application role? I suspect that may be the root of my misunderstanding on this. As I stated above when the application is initialized, I EXECUTE the set application role with a given connection that only has local scope inside that subroutine.

Now I am looking at the rest of her code where she has written various subroutines to "do stuff" and they are typically characterized by something like:

        Dim conn As SqlConnection = New SqlConnection(UserConnstr)

where: UserConnstr has a value of:
Data Source=MDWDATA;Initial Catalog=MDS;Integrated Security=True

which I think explains how the credentials of the logged on user are being used (rather than using the application role I had hoped to simply establish at "init" time and be done with that issue).

So, in summary, does use of an application role require a globally persistent connection or some other technique beyond what I've described here?

Answer 1

did you check to make sure the user doesn't have any explicit denies on dbo or the table? Also, is the user's windows login mapped to the MDS database?