Francisco Tapia
fhtapia at gmail.com
Sat Jun 18 12:31:02 CDT 2011
I'd check that too, it seems that maybe your user has data reader access which gives full select rights to tables and views but not exec rights to sprocs. Inmate checkout eh? Maybe lock that connection down beyond data reader access too. I'm just saying. Sent from my mobile On Jun 17, 2011, at 8:01 PM, Stuart McLachlan <stuart at lexacorp.com.pg> wrote: > 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 >> >> > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >