Actually I don't think it is a permission issue.
After doing google search for Must declare the scalar variable I found all sorts of things.
In the case of DNN, some developer is fat fingering the keyboard.
I downloaded 4.3.7 and installed per the instructions. And ran in to all sorts of scalar errors.
To correct the issue in the install of 4.3.7 I had to open a lot of files, do a lot of search and replace.
An example. In this example @PortalID is happy about itself. But if you go in and change the first @PortalID to @PortalId then SQL whines about scalar values. You will find numerous instances in other procedures where it's @name and @Name, or @modelID and @ModelID. Best thing to do is find out where it's complaining and then do a search and replace, that's what I ended up doing. Scalar errors would also prevent other things from happening. You might have seen functions missing and such. That's because the previous function might have had a scalar error and didn't get created.
CREATE PROCEDURE {databaseOwner}{objectQualifier}AddPropertyDefinition
@PortalID int,
@ModuleDefId int,
@DataType int,
@DefaultValue nvarchar(50),
@PropertyCategory nvarchar(50),
@PropertyName nvarchar(50),
@Required bit,
@ValidationExpression nvarchar(100),
@ViewOrder int,
@Visible bit,
@Length int
AS
DECLARE @PropertyDefinitionId int
SELECT @PropertyDefinitionId = PropertyDefinitionId
FROM {objectQualifier}ProfilePropertyDefinition
WHERE (PortalId = @PortalID OR (PortalId IS NULL AND @PortalID IS NULL))
AND PropertyName = @PropertyName
IF @PropertyDefinitionId is null
BEGIN
INSERT {objectQualifier}ProfilePropertyDefinition (
PortalId,
ModuleDefId,
Deleted,
DataType,
DefaultValue,
PropertyCategory,
PropertyName,
Required,
ValidationExpression,
ViewOrder,
Visible,
Length
)
VALUES (
@PortalID,
@ModuleDefId,
0,
@DataType,
@DefaultValue,
@PropertyCategory,
@PropertyName,
@Required,
@ValidationExpression,
@ViewOrder,
@Visible,
@Length
)
SELECT @PropertyDefinitionId = SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE {objectQualifier}ProfilePropertyDefinition
SET DataType = @DataType,
ModuleDefId = @ModuleDefId,
DefaultValue = @DefaultValue,
PropertyCategory = @PropertyCategory,
Required = @Required,
ValidationExpression = @ValidationExpression,
ViewOrder = @ViewOrder,
Deleted = 0,
Visible = @Visible,
Length = @Length
WHERE PropertyDefinitionId = @PropertyDefinitionId
END
SELECT @PropertyDefinitionId
GO
But back to my origianal error. I'm going to go in and find the bloody code that's causing the problem and fix the scalars and see if it goes away. In the mean time I'm going to file a bug report regarding the 4.3.7 files. Some of the developers must have their databases setup to ignore case and do the ol' axium - Do as I say, not as I do.
So stay tuned!