EXECUTE permission denied on object, database , owner 'dbo'.

Question

I successfully executed a stored procedure on a SQL2005.When I wanted to populate the results in a form via CFSTOREDPROC , I am getting the following error.

    [Macromedia][SQLServer JDBC Driver][SQLServer]EXECUTE permission denied on object 'GetPSRreportStock', database 'CGTSP_GET',

  <cfstoredproc procedure="FP_Get..GetStartStopTotalBalesCott" datasource="#TS#" username="#UNT#" password="#SPW#">

      <cfprocparam  type="in" cfsqltype="CF_SQL_INTEGER" dbvarname="@reportYear"  value="#xxMDB#">
        <cfprocparam  type="in" cfsqltype="CF_SQL_INTEGER" dbvarname="@orderMonth"     value="#xxOBDB#">
        **<cfprocresult name="ccDPR">**
    </cfstoredproc>

The CF code is in MX-6. How to correct the error?

Answer 1

I think it's because you need to give the user your web server is running as EXECUTE permissions for that stored procedure (in the SQL Server management console).

Answer 2

Check to make sure the user trying to run the stored proc has execute permissions, you can do this in SQL Server Management Studio by going to the Database -> Programmability -> and Right Click on the Stored Proc and select 'Properites', then go to the 'Permissions' section and see if your user is there, if its not, you can add the user and give it EXECUTE permissions, or you can do the following in a new query:

GRANT EXECUTE TO <username> ON <stored proc name>