Software

Ask something
Question Locked

Application Roles in VBA

0 helpful
4 replies

by dynic-it

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello

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;"

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

Thanks

4 replies

by tony hopkinson

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.

http://msdn.microsoft.com/en-us/library/bb669062.aspx

by dynic-it

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

by tony hopkinson

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

try User ID instead of UID.

A late thought...

This discussion is closed to new replies.