EXECUTE permission denied error

[rivkahannah]

Hi,
I am a newbie to SQL Server. I have a SQL Server 2000 database that has an Access 2000 front end. I set up several stored procedures that are called using pass through queries in the front end. On my test server, they run fine and on a second database that I also set up with the same front-end/back end schema they work fine also. However, on the production server the stored procedures keep giving me this error: EXECUTE permission denied on object 'xp_TempSite', database 'reimb', owner 'dbo'

The front end uses an application log in that I set up and I have granted execute permissions on all of the sp's. I have tried revoking and re-granting the permissions but it doesnt seem to help. I can also run views through the pass through queries without any errors.

Please help!

Thank you!!
Rebekah

 

[SQLBill]

Obviously there is something different with your production database.

What authentication type are you using on the Production DB? On the test DB?

What login are you using to access the Production db? Is it the same as when you used the test db?

You granted execute permissions, but does the login have access to the database? (In Enterprise Manger go to the database and make sure the user is listed there).

-SQLBill

 

[RobertT687]

Check the ODBC Connect String used by your pass-thru query in Access. Make sure the login id/pwd used in the connect string is correct and is a login you granted access/permissions to.

To see the connect string, open the query in design mode. Click View -> Properties

 

[rivkahannah]

I am using SQL Server authentication for both databases and I created a login on each db that is exactly the same for both. Each login does have access to its respective database.

One thing that I forgot to mention before is that when I run the stored procedure from a remote location it runs fine, but when I run it from a computer located directly on the network (in the same building as the server), I get this error.

Could this be a problem with the DSN that is created/set up through the front end? I supply the user and password when I execute the pass through query but it seems like it may be ignoring it, I just went into the DSN and manually set the username and password and the sp worked but when I closed out and tried it again it went back to the error.