[dba-SQLServer] Execute permission denied

jwcolby jwcolby at colbyconsulting.com
Fri Jun 17 21:50:50 CDT 2011


I am trying to figure out how to use stored procedures to return recordsets to Access.  I created a 
stored procedure that accepts a parameter, the stored procedure pulls a recordset and if used like so:

exec usp_MySP 1

returns a recordset to the query window in SQL Server.

So, now how to get it to work in Access.  I thought I was going to create a pass through query 
dynamically, save it and then "open" the query to get the recordset.

The query is a passthrough query

usp_InmatesForVolunteers 1

The ODBC connect string is:

ODBC;DRIVER=SQL Server;UID=MyUser;PWD=MyPassword;SERVER=5.58.170.179;DATABASE=InmateCheckout;

All of this (except for the database part) comes directly out of a DSN file which works to link 
tables and views into Access.  Tested and working for that purpose.

So the passthrough query fails.  Error:

[Microsoft][ODBC Sql Server Driver][SQL Server]The execute permission was denied on the object 
'usp_InmatesForVolunteers', database 'InmateCheckout', schema 'dbo'. (#229)

Interestingly if I remove the database part of the connection string it returns exactly the same 
error message, even mentioning that database.  Thus the user itself being mapped to that database 
seems to be working as well.

As I said, this all works for linking SQL Server tables and views, just not executing the sp.

Any ideas?

-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list