I'm executing a SQL 2008 sproc from an Access Data Project, using ADO. The sproc truncates and then fills a table in the SQL database, which is then used as the recordsource in an Access report.

This works fine for all users except one. I assumed it was a problem with that user's privileges, but when I connected to the database thru SSMS using his login, I was able to excecute the sproc successfully. I even explicitly granted that SELECT, UPDATE and DELETE to that user but it stills throws the error when executed from Access.

Any sugges

1. Be sure that you use the Query for the appropriate DataBase like:

Use YouDataBase

Go

YourQuery

2. The customer open his Windows Session from a Domain? or do you use a SQL Server login?

3. Try to connect with the login of you customer from another machine.

4. Try to install the latest service pack and updates of Access.

5. Try to create an ODBC connection in the machine of the customer with his login.

6. Try this : http://stackoverflow.com/questions/1601562/cannot-find-the-object-xxx-because-it-does-not-exist-or-you-do-not-have-permis

Hello,

What happen if you make the application call the store procedure using dbo like dbo.storeprocedurename1?

What is the default database for that user? Is it the same as the other users?

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

I tried your suggestion #5.

I logged in as 'casey' (the userid that received the error) and was able to connect to the server using the DSN. But when I selected the table it threw this error:
"ODBC--call failed. CREATE TABLE permission denied in database 'Inventory'. (#262). I thought this was odd since the sproc doesn't do a CREATE TABLE. But the sproc does a TRUNCATE TABLE followed by an INSERT. When I changed TRUNCATE TABLE to a DELETE FROM, the error went away. As it turns out, I hadn't assigned a CREATE TABLE permission to that user, only SELECT, INSERT, UPDATE, and DELETE. I guess TRUNCATE followed by INSERT must trigger a CREATE TABLE?

TRUNCATE TABLE requires ALTER permission on the table. You could say that TRUNCATE TABLE is half of DROP TABLE. The pages are deallocated, but the metadata is retained.

Rather that granting users extra rights, a better solution is to sign the procedure with a certificate, create a user from the certificate grant that user ALTER on table. In this way, you package the permission with the procedure. (The user for the certificate is special kind of user that cannot log in.)

I have a longer article on my web site that describes this technique in detail:
http://www.sommarskog.se/grantperm.html

Thanks Erland, I didn't realize that a TRUNCATE required ALTER permission. I'll try your suggestion.