I have a rather strange problem concerning a stored procedure. The error is:
System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'CountOrders', database 'DNN_EXTRA', schema 'dbo'.
Now the way i read this is that i need to add execute permission to the stored proc CountOrders for the user that runs it. Now this is where it gets odd. This proc has been running no problem for ages. That is, it runs fine when someone is logged into DNN. Be it admin host or regular user. But when an unregistered user tries to run the proc i get the above error. Now correct me if i'm wrong but that shouldn't make any difference what so ever.
The code that runs the proc is creating an order reference based on wether a user is logged in or not and what type of user they are (i have ACCOUNT_CUST, REG_CREDIT_CUST, and UNREG_CREDIT_CUST). It then builds a string using StringBuilder out of various values. One of the last value to be added is generated from the stored proc and is run by all users. Here is the code:
order = new Order();
order.BillingCustomer = new Customer();
StringBuilder str = new StringBuilder();
str.Append(DateTime.Today.Day);
str.Append(DateTime.Today.Month);
// Determine what type of user is making the order
if (this.UserId >= 0)
{
if (IsAccountCustomer())
{
// User is an account customer
order.AccountType = Order.ACCOUNT_CUST;
order.Account = UserInfo.Username;
str.Append("01");
str.Append(UserId);
}
else
{
// User is a registered credit card customer
order.AccountType = Order.REG_CREDIT_CUST;
order.Account = UserId.ToString();
str.Append("02");
str.Append(UserId);
}
}
else
{
// User is an unregistered credit card customer
order.AccountType = Order.UNREG_CREDIT_CUST;
order.Account = "000000";
str.Append("030");
}
str.Append((int)DataProvider.Instance.ExecuteSqlScalar("SiteData", "dbo.CountOrders"));
order.CustomerRef = string.Empty;
order.OrderType = str.ToString();
Here is the ExecuteSqlScalar method:
public object ExecuteSqlScalar(string connection, string storedProcName, params object[] commandParameters)
{
return SqlHelper.ExecuteScalar(Config.GetConnectionString(connection), storedProcName, commandParameters);
}
Anybody got any ideas? This has completely stumped me.
Simon