[dba-SQLServer] Execute permission denied

exec usp_InmatesForVolunteers 1

At 10:00 PM 6/17/2011, you wrote:
>Date: Fri, 17 Jun 2011 22:50:50 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>To: Access Developers discussion and problem solving
>         <accessd at databaseadvisors.com>,         Sqlserver-Dba
>         <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] Execute permission denied
>Message-ID: <4DFC128A.4010202 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>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

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com