Need Permission to Execute Stored Procedure?

[seadriver]

Hi guys and gals,

Sorry for the newbie-type question, but I haven't found the answer elsewhere.

A company I do some work for recently relocated one of their databases to a remote server, which we access through SQLOLEDB, with the IP address as the data source in the connection string. I believe the db was created using the db recreation scripts, then restored from a backup.

The VB application that maintains the membership db is now unable to add rows to a table, using a stored procedure that's accessed through ADO, like this:

Dim cmdTemp As New ADODB.Command
With cmdTemp
    .ActiveConnection = DB.TransAction
    .CommandType = adCmdStoredProc
    .CommandText = "SP_ADD1_MEMBER"
    .Parameters.Refresh
    .Parameters("@MemberNo") = lngMemberNo
    .Parameters("@FirstName") = strFirstName
    .Parameters("@LastName") = strLastName
.
.
.
    .Parameters("@UserName") = strUserNameHold
End With

cmdTemp.Execute

Trying to set the first parameter's value to lngMemberNo triggers the error message you get when you refer to a column name that doesn't exist, something like "Item name or ordinal number not found in the collection", or some such.

I've confirmed that the SP exists on the remote db, and the parameter names all match. When I set the connection back to the "old" local db, it works fine.

Do I need to give the application's user login specific permission to execute stored procedures, or add rows to tables? If so, can I do that through Enterprise Manager, or would it have to be done by someone who has admin privs to the db locally?

TIA for any advice you can offer.

 

[donutman]

I see something that you shouldn't be doing, although I seriously doubt that it causes the problem. The preferred construct for defining an object in VB6 (just the opposite for .NET):

Dim cmdTemp As ADODB.Command
Set cmdTemp = New adodb.Command

I'm fairly sure that will only improve overall performance, not fix your problem. Sorry, I know very little about permissions.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[seadriver]

The "set" line is in the actual code; I mistakenly deleted it when I abstracted the code to present my problem.

Thanks anyway!

 

[donutman]

It's the placement of the New keyword that is critical.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[seadriver]

Sorry, you're right, of course.

Still need an answer to the original problem...

 

[donutman]

See if it makes any difference if you use .Parameters(1) etc. instead of "@MemberNo". Also create a test SP that doesn't have any parameters and see if that runs.
Just things to try until someone with permission knowledge shows up.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Yes you can set permissions to a database in Enterprise manager if you have admin rights and you are connected to the database and can see it in Enterprise Manager.

You can try setting exec permission onthe sp for the application login. But if you used any dynamic SQL you will have to set the permissions on the tables.

Questions about posting. See faq183-874

 

[seadriver]

Thanks, SQL Sister.

In comparing the remote and the local db's, I noticed that the admin of the remote db created a separate user account for the app's login user id, while the local db has only public and... I forget the other one.

So, I'm guessing (I told you I was a newbie) that on the local db, the app has the privileges assigned to public, while on the remote db, it needs to have its own permissions set, since it's been given its own account.

I'm sorry if my terminology is not correct, but does that sound right?

 

[SQLSister]

Yes, set the permission for the application login. You should never set permissions at the public level anyway as that is a bad security practice.

Questions about posting. See faq183-874