[dba-SQLServer] Execute permission denied

Jim,

In fact I had done all of that, though I made the SP directly in SQL Server.  It turned out to be a 
permissions issue.  There were no users specifically allowed to execute the sp.  Once I did that it 
looks just like a query in Access.  I will need to do things like rebuild the pass through queries 
when a new user logs in since each user has one or more specific camp (s)he visits.

I do so using code similar to yours below.

John W. Colby
www.ColbyConsulting.com

On 6/18/2011 7:38 PM, Jim Lawrence wrote:
> Hi John:
>
> It can not be too different from the standard method:
>
> /*Stored Procedure
> CREATE PROC dbo.RECompanyConfigurationGET
>    @intCompanyCode int
> AS
>    SELECT DISTINCT CompanyConfig.CompanyCode, CasinoCompany.CompanyName,
> 	CompanyConfig.InitialLetterLocation,
> CompanyConfig.FollowupLetterLocation, CompanyConfig.ExpiryLetterLocation
> 	FROM CasinoCompany INNER JOIN CompanyConfig ON
> CasinoCompany.CompanyCode = CompanyConfig.CompanyCode
> 	WHERE CompanyConfig.CompanyCode = @intCompanyCode
> 	ORDER BY CasinoCompany.CompanyName;
> GO
>
> ------------------------------------------------
>
> 'Calling code from Access
> Dim rs1 As ADODB.Recordset
> Dim objCmd As ADODB.Command
>
> Set rs1 = New ADODB.Recordset
> Set objCmd = New ADODB.Command
>
> With objCmd
>    .ActiveConnection = gstrConnection 'Connection string
>    .CommandText = "RECompanyConfigurationGET"
>    .CommandType = adCmdStoredProc
>    .Parameters.Append .CreateParameter("@intCompanyCode", adInteger,
> adParamInput, , typCmpConf.CompanyCode)
> End With
>
> With rs1
>    .CursorLocation = adUseClient
>    .Open objCmd, , adOpenStatic, adLockReadOnly
>    If .BOF = False Or .EOF = False Then
>      .MoveLast
>      ...
>    End if
> End With
>
> Jim
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, June 17, 2011 7:51 PM
> To: Access Developers discussion and problem solving; Sqlserver-Dba
> Subject: [dba-SQLServer] Execute permission denied
>
> 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?
>