EXECUTE authorized permission on object, database, owner dbo '
I have successfully executed a stored procedure on SQL2005 When I wanted to fill in the results in a form via CFSTOREDPROC I get 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 found in the MX-6. How do I fix the error?
Make sure the user trying to start the stored process has execute permissions, you can do this in SQL Server Management Studio by going to Database -> Programmability -> and right click on the saved Proc version and select "Properites" then go to See the Permissions section and see if you have your user, if not, you can add the user and grant him EXECUTE permissions, or you can do the following in a new request:
GRANT EXECUTE TO <username> ON <stored proc name>
I think because you need to provide the user with your web server running EXECUTE for this stored procedure (in the SQL Server Management Console).
Go to Properties in each stored procedure in SQL Server, then select Permissions on the left side, then add the user you are using by searching on the Search ... button, then click the Check Now box. Then ok and ok.