[dba-SQLServer] Execute permission denied

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?

-- 
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