/*This Script will add all Permissions for the Portal Folder to a specific role - Please note that this script does not check if the permission already exists since that would decrease performance. 
It will throw errors for duplicated inserts but will complete as intended on SSMS*/

DECLARE @PortalID AS INT;
DECLARE @RootFolderID AS INT;
DECLARE @AddRoleID AS INT;
DECLARE @RoleName AS VARCHAR(20) = 'Administrators'
DECLARE @AllowAccess AS INT = 1

DECLARE @PortalCursor AS CURSOR;

DECLARE @ReadFolderPermission AS INT;
DECLARE @WriteFolderPermission AS INT;
DECLARE @BrowseFolderPermission AS INT;
DECLARE @AddFolderPermission AS INT;
DECLARE @CopyFolderPermission AS INT;
DECLARE @DeleteFolderPermission AS INT;
DECLARE @ManageFolderPermission AS INT;

/*Get all Permission IDS*/
SELECT @ReadFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'READ';
SELECT @WriteFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'WRITE';
SELECT @BrowseFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'BROWSE';
SELECT @AddFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'ADD';
SELECT @CopyFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'COPY';
SELECT @DeleteFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'DELETE';
SELECT @ManageFolderPermission = PermissionId FROM Permission WHERE PermissionCode = 'SYSTEM_FOLDER' AND PermissionKey = 'MANAGE';


/*Get List of all Portals.*/
SET @PortalCursor = CURSOR FOR

  SELECT PortalID  
  FROM Portals

OPEN @PortalCursor;

FETCH NEXT FROM @PortalCursor INTO @PortalID;  

  WHILE @@FETCH_STATUS = 0
    BEGIN 

	  /*Get Root Folder ID and Role ID*/
	  SELECT @RootFolderID = FolderID FROM Folders WHERE FolderPath = '' AND PortalID = @PortalID;
	  SELECT @AddRoleID = RoleID FROM Roles WHERE RoleName = @RoleName AND PortalID = @PortalID;

	  /* Add all permissions for the Role - This SP does not check if it exists */
	  EXEC AddFolderPermission @RootFolderID, @ReadFolderPermission, @AddRoleID, @AllowAccess, 1, 1;
	  EXEC AddFolderPermission @RootFolderID, @WriteFolderPermission, @AddRoleID, @AllowAccess, 1, 1;
	  EXEC AddFolderPermission @RootFolderID, @BrowseFolderPermission, @AddRoleID, @AllowAccess, 1, 1;
	  EXEC AddFolderPermission @RootFolderID, @AddFolderPermission, @AddRoleID, @AllowAccess, 1, 1;
	  EXEC AddFolderPermission @RootFolderID, @CopyFolderPermission, @AddRoleID, @AllowAccess, 1, 1;
	  EXEC AddFolderPermission @RootFolderID, @DeleteFolderPermission, @AddRoleID, @AllowAccess, 1, 1;
	  EXEC AddFolderPermission @RootFolderID, @ManageFolderPermission, @AddRoleID, @AllowAccess, 1, 1;

      FETCH NEXT FROM @PortalCursor INTO @PortalID;
    END

CLOSE @PortalCursor;
DEALLOCATE @PortalCursor;