I have seen requests for being able to add DNN Roles by Products posted in this forum before so I thought I would post this.   I added two triggers to the Store_Orders table and created a new table to store the ProductIDs (from the Store_Products table) and the RoleIDs (in the Roles table) that I want added for each product. There are no doubt better ways to code this than with triggers but I needed a quick and dirty solution.  This post is only intended for DBAs and SQL programmers with access to the DNN database and is just an idea that worked for me - not an out of the box solution.  I used Store (02.01.00) and DNN 04.07.00.

It is a cludgy solution at the moment since I need to manually add the product and role IDs to the table and if my products change I could be adding the wrong roles.  The triggers add the roles whenever the OrderStatus is changed to 7 (paid). I have also hard coded the portal ID to 0 since my site only has one portal.  The solution also uses triggers which can get you into trouble if you are not careful.  I have only flame tested the triggers but they seem to do what I need.  The triggers add roles for Host accounts as well which would not be needed.  Please only try this on a test system and create backups first.  Here is the code to create the two triggers and the extra table:

CREATE TABLE [dbo].[v05_Store_ProductRoles](
 [ProductRoleID] [int] IDENTITY(1,1) NOT NULL,
 [ProductID] [int] NOT NULL,
 [RoleID] [int] NOT NULL,
 CONSTRAINT [PK_v05_Store_ProductRoles] PRIMARY KEY CLUSTERED
(
 [ProductRoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[v05_Store_Orders_UT] ON [dbo].[Store_Orders]
FOR UPDATE
AS

IF  (SELECT OrderStatusID FROM INSERTED) = 7 --PAID
 BEGIN
  DECLARE @RC int
  DECLARE @PortalId int
  DECLARE @UserId int
  DECLARE @RoleId int
  DECLARE @EffectiveDate datetime
  DECLARE @ExpiryDate datetime
 
  DECLARE @myOrder int
  SELECT @myOrder = OrderID from INSERTED

  -- TODO: Set parameter values here.
  SELECT @PortalId = 0
  SELECT @UserId = UserID from INSERTED
 
  DECLARE ProductRoles CURSOR FOR
   SELECT RoleID FROM dbo.v05_Store_ProductRoles SPR
   inner join dbo.Store_OrderDetails SOD ON SPR.ProductID = SOD.ProductID
   WHERE SOD.OrderID = @myOrder
  OPEN ProductRoles
  FETCH NEXT FROM ProductRoles INTO @RoleID
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
   EXECUTE @RC = [DotNetNuke].[dbo].[AddUserRole]
      @PortalId
     ,@UserId
     ,@RoleId
     ,@EffectiveDate
     ,@ExpiryDate
   FETCH NEXT FROM ProductRoles INTO @RoleID
  END
 
  CLOSE ProductRoles
  DEALLOCATE ProductRoles
 END
GO

CREATE TRIGGER [dbo].[v05_Store_Orders_IT] ON [dbo].[Store_Orders]
FOR INSERT
AS

IF  (SELECT OrderStatusID FROM INSERTED) = 7 --PAID
 
 BEGIN

  DECLARE @RC int
  DECLARE @PortalId int
  DECLARE @UserId int
  DECLARE @RoleId int
  DECLARE @EffectiveDate datetime
  DECLARE @ExpiryDate datetime
 
  DECLARE @myOrder int
  SELECT @myOrder = OrderID from INSERTED

  -- TODO: Set parameter values here.
  SELECT @PortalId = 0
  SELECT @UserId = UserID from INSERTED
 
  DECLARE ProductRoles CURSOR FOR
   SELECT RoleID FROM dbo.v05_Store_ProductRoles SPR
   inner join dbo.Store_OrderDetails SOD ON SPR.ProductID = SOD.ProductID
   WHERE SOD.OrderID = @myOrder
  OPEN ProductRoles
  FETCH NEXT FROM ProductRoles INTO @RoleID
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
   EXECUTE @RC = [DotNetNuke].[dbo].[AddUserRole]
      @PortalId
     ,@UserId
     ,@RoleId
     ,@EffectiveDate
     ,@ExpiryDate
   FETCH NEXT FROM ProductRoles INTO @RoleID
  END
 
  CLOSE ProductRoles
  DEALLOCATE ProductRoles

 END
GO

Yes. Add the RoleID from the Roles table in DNN that you want to unlock along with the ProductID from the Store_Products table. You will need to update the table as you add or delete products or roles. Note that you can unlock as many roles as you want by simply listing multiple roles for the same product in the table. Whether you use PayPal IPN or the drop down list in the store to change the Status to Paid the trigger should still function because it operates at the table level. The portal is hard coded to 0 which is the root portal but if you run sub portals that will not work. I had this working in DNN 4.9 with Store 2.1.28 but I have had issues with PayPal IPN in version 5.2.1 of DNN. DNN 5.2.3 seems to have fixed the IPN issue along with the subscription link to PayPal in DNN itself. I am not done getting my site fully working on 5.2.3 yet but initial testing is promising. Please let me know how it goes?

Never Mind i Manage to fix it.

 I add the value to the triggers and works perfect i own you some beers. Here is my code.

CREATE TRIGGER [dbo].[v05_Store_Orders_UT] ON [dbo].[Store_Orders]
FOR UPDATE
AS

IF  (SELECT OrderStatusID FROM INSERTED) = 7 --PAID
 BEGIN
  DECLARE @RC int
  DECLARE @PortalId int
  DECLARE @UserId int
  DECLARE @RoleId int
  DECLARE @EffectiveDate datetime
  DECLARE @ExpiryDate datetime
  DECLARE  @CreatedByUserIDvarchar(20)
 
  DECLARE @myOrder int
  SELECT @myOrder = OrderID from INSERTED

  -- TODO: Set parameter values here.
  SET @CreatedByUSERID = (user_id())
  SELECT @PortalId = 0
  SELECT @UserId = UserID from INSERTED
 
  DECLARE ProductRoles CURSOR FOR
   SELECT RoleID FROM dbo.v05_Store_ProductRoles SPR
   inner join dbo.Store_OrderDetails SOD ON SPR.ProductID = SOD.ProductID
   WHERE SOD.OrderID = @myOrder
  OPEN ProductRoles
  FETCH NEXT FROM ProductRoles INTO @RoleID
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
   EXECUTE @RC = [DotNetNuke].[dbo].[AddUserRole]
      @PortalId
     ,@UserId
     ,@RoleId
     ,@EffectiveDate
     ,@ExpiryDate
     ,@CreatedByUserID
   FETCH NEXT FROM ProductRoles INTO @RoleID
  END
 
  CLOSE ProductRoles
  DEALLOCATE ProductRoles
 END
GO

CREATE TRIGGER [dbo].[v05_Store_Orders_IT] ON [dbo].[Store_Orders]
FOR INSERT
AS

IF  (SELECT OrderStatusID FROM INSERTED) = 7 --PAID
 
 BEGIN

  DECLARE @RC int
  DECLARE @PortalId int
  DECLARE @UserId int
  DECLARE @RoleId int
  DECLARE @EffectiveDate datetime
  DECLARE @ExpiryDate datetime
  DECLARE @CreatedByUserID varchar(20)
 
  DECLARE @myOrder int
  SELECT @myOrder = OrderID from INSERTED

  -- TODO: Set parameter values here.
  SET @CreatedByUSERID = (user_id())
  SELECT @PortalId = 0
  SELECT @UserId = UserID from INSERTED
 
  DECLARE ProductRoles CURSOR FOR
   SELECT RoleID FROM dbo.v05_Store_ProductRoles SPR
   inner join dbo.Store_OrderDetails SOD ON SPR.ProductID = SOD.ProductID
   WHERE SOD.OrderID = @myOrder
  OPEN ProductRoles
  FETCH NEXT FROM ProductRoles INTO @RoleID
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
   EXECUTE @RC = [DotNetNuke].[dbo].[AddUserRole]
      @PortalId
     ,@UserId
     ,@RoleId
     ,@EffectiveDate
     ,@ExpiryDate
     ,@CreatedByUserID
   FETCH NEXT FROM ProductRoles INTO @RoleID
  END
 
  CLOSE ProductRoles
  DEALLOCATE ProductRoles

 END
GO