I wrote a stored procedure for my application & can successfully run it in on my local. But while running the same application live it's throwing an error:
Executing stored procedures is not allowed
Issue return while run the site.
This is my sample code :
<cfstoredproc procedure="countUsers" datasource="myDataSource" debug="yes" result="countResults">
<!--- ClientID --->
<cfprocparam cfsqltype="cf_sql_integer" value="#customerID#" type="IN">
<!--- LoggedInNow --->
<cfprocparam cfsqltype="cf_sql_bit" value="1" type="IN">
<cfprocresult name="qUsers" maxrows="5">
</cfstoredproc>
While dump the query result it's return data. But while test with live site it's return the below issue,
Note: But I can directly run the procedure in SQL Server directly with my same login.
Only the difference is in my local using cf11 but the live is in cf18.
To grant permissions on a stored procedureExpand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search.
Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.
If you've READ permission on database, you can read data only from Tables, Views, and Functions. But to execute stored procedures, you need to provide permission explicitly to user. There are multiple ways you can provide execute permission to any user.
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
As per beginner suggestion, I've enable the stored procedure options in cf administrator. And I fixed that issue. To enable that options follow my below steps.
Please refer to this screen here:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With