Got an error page in the Public Store after adding an accessory to a product. The customer had access to the Product but not the Accessory. The PurchaseModule was attempting to load an accessory that the customer didn't have access to and could not create the URL for the accessory. Looking into this, why is the accessory coming back at all? I found the answer in the three ProductAccessoryLoad* procedures and comparing this to the CrossSellingLoad* procedures came up with this:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ProductAccessoryLoadByPrimaryKey]
(
@ProductAccessoryId int,
@CustomerId int,
@AccessLevel int = 1,
@ShowHidden bit = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
declare @product_type as int
set @product_type= 1
SELECT
PA.[ProductAccessoryId],
PA.[ProductId],
PA.[AccessoryId],
PA.[Ordering]
FROM [ProductAccessory] PA
INNER JOIN [Product] P ON P.[ProductId]=PA.[ProductId]
INNER JOIN [Product] P1 ON P1.[ProductId]=PA.[AccessoryId]
WHERE ([ProductAccessoryId] = @ProductAccessoryId)
AND ((P.Visible = 1 AND [dbo].[IsObjectAccessGranted](PA.ProductId, @product_type, @CustomerId, @AccessLevel) = 1) or @ShowHidden = 1)
AND ((P1.Visible = 1 AND [dbo].[IsObjectAccessGranted](PA.AccessoryId, @product_type, @CustomerId, @AccessLevel) = 1) or @ShowHidden = 1)
SET @Err = @@Error
RETURN @Err
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ProductAccessoryLoadByProductID]
(
@ProductId int,
@CustomerId int,
@AccessLevel int = 1,
@ShowHidden bit = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
declare @product_type as int
set @product_type= 1
SELECT
PA.[ProductAccessoryId],
PA.[ProductId],
PA.[AccessoryId],
PA.[Ordering]
FROM [ProductAccessory] PA
INNER JOIN [Product] P ON P.[ProductId]=PA.[ProductId]
INNER JOIN [Product] P1 ON P1.[ProductId]=PA.[AccessoryId]
WHERE (P.[ProductId] = @ProductId)
AND ((P.Visible = 1 AND [dbo].[IsObjectAccessGranted](PA.ProductId, @product_type, @CustomerId, @AccessLevel) = 1) or @ShowHidden = 1)
AND ((P1.Visible = 1 AND [dbo].[IsObjectAccessGranted](PA.AccessoryId, @product_type, @CustomerId, @AccessLevel) = 1) or @ShowHidden = 1)
ORDER BY PA.[Ordering]
SET @Err = @@Error
RETURN @Err
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ProductAccessoryLoadAll]
@CustomerId int,
@AccessLevel int = 1,
@ShowHidden bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
declare @product_type as int
set @product_type= 1
SELECT
PA.[ProductAccessoryId],
PA.[ProductId],
PA.[AccessoryId],
PA.[Ordering]
FROM ProductAccessory PA
INNER JOIN [Product] P ON P.[ProductId]=PA.[ProductId]
INNER JOIN [Product] P1 ON P1.[ProductId]=PA.[AccessoryId]
WHERE
((P.Visible = 1 AND [dbo].[IsObjectAccessGranted](PA.ProductId, @product_type, @CustomerId, @AccessLevel) = 1) or @ShowHidden = 1)
AND ((P1.Visible = 1 AND [dbo].[IsObjectAccessGranted](PA.AccessoryId, @product_type, @CustomerId, @AccessLevel) = 1) or @ShowHidden = 1)
ORDER BY PA.[ProductId], PA.[Ordering]
SET @Err = @@Error
RETURN @Err
END
GO