[dba-SQLServer] Execute permission denied

Have you specifically set Execute permissions on the SP for MyUser?

-- 
Stuart

On 17 Jun 2011 at 22:50, jwcolby wrote:

> 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=InmateCh
> eckout;
> 
> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>