Application Roles in VBA

By Dynic-IT ·

Can someone please help. I am trying to connect to a SQL2005 database through VBA in Excel. I have set up a Application Role with permission to select from my database, and it works fine from my computer, but it doesn't work on a regular users computer.

I'm a full administrator, and the users computer gives a "Type mismatch" error when I try and open the connection, which makes me think that the logon is using Windows authentication and ignoring the Application Role

Connection String:
"Provider=SQLOLEDB;server=myserver;Initial Catalog=mydatabase;Integrated Security=SSPI;UID=myapplicationrole; Pwd=mypassword;"

Dim rs As ADODB.Recordset
Dim objConnection As New ADODB.Connection
objConnection.Open myconnectionstring
Set rs= objConnection.Execute("SELECT STATEMENT")


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

You told it to use windows authentication

by Tony Hopkinson In reply to Application Roles in VBA

with integrated security=SSPI

Application Roles sit on top of user level security.

It's a way of saying User Fred when running Application MyApp can do this and that
and when running Application MyOtherApp can do this but not that.

Last I saw you don't set role in the connection string, but with the sp_SetAppRole stored procedure.

Perhaps this will help, it's not something I've actually done, so I won't be a big help I'm afraid.

Collapse -


by Dynic-IT In reply to You told it to use window ...

I haven't checked the link, but I researched the stored procedure you mentioned.

The connection string should be "UID=;Psw=", and then you need to run an SQL command/statement through your connection in your code:
sp_SetAppRole 'MyAppRole','AppRolePassword'

You do however ned to give the user a security login profile, so that they are able to run the stored proc

Thanks for your help

Collapse -

Still not sure on the type mismatch

by Tony Hopkinson In reply to sp_SetAppRole

Suspect that may be an old dll on the client or some such.

try User ID instead of UID.

A late thought...

Related Discussions

Related Forums