Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Executing stored procedures is not allowed

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,

enter image description here

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.

like image 241
Kannan.P Avatar asked Apr 09 '19 07:04

Kannan.P


People also ask

How do I grant permission to run a stored procedure?

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.

How do you execute a stored procedure?

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.

What permissions are required to execute stored procedure?

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.

Can you execute a stored procedure in the database?

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.


1 Answers

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.

  1. Go to cf admin
  2. While add / edit the data source name
  3. Click on show advance setting options
  4. Now you can see Allowed SQL options. In that option you have number of check boxes. Here you have to check the stored procedures options
  5. Click the submit button. Now check with your site the stored procedure gets run.

Please refer to this screen here:

enter image description here

like image 55
Kannan.P Avatar answered Sep 30 '22 19:09

Kannan.P