It would take me tens of thousands of clicks to set all the permissions on all my categories on all my portals. So, I wrote the following script to set the permissions on all my CB categories and portals at once. It gives permissions to all users to view, comment and rate and administrators for everything else. You will find the following section that defines which portals to set permissions on "WHERE PortalID IN (0,2,4,9,10,11,12,13,17,21,22,23)" If you want permissions set on all portals, just remove this entire line. Otherwise, just replaced your portalids in this string. Please backup your db before running it. Run at your own risk. Works great for me.
Enjoy, John
---
/*** This script gives the correct permissions to all roles on all portals in DNNMasters CB ***/
DECLARE
@PermDefID INT,
@ClassificationID INT,
@PortalID INT,
@AdminRoleID INT,
@RoleID INT
SET NOCOUNT ON
/* Create cursor containing all PortalIDs for first loop */
DECLARE PermDefIDCursor CURSOR FAST_FORWARD FOR
SELECT PermDefID
FROM DNNMasters_CB_Permissions_Definitions
OPEN PermDefIDCursor
FETCH NEXT FROM PermDefIDCursor INTO @PermDefID
WHILE @@FETCH_STATUS = 0
BEGIN
/* Create cursor containing all ClassificationIDs for second loop */
DECLARE ClassificationIDCursor CURSOR FAST_FORWARD FOR
SELECT ClassificationID
FROM DNNMasters_CB_Classifications
OPEN ClassificationIDCursor
FETCH NEXT FROM ClassificationIDCursor INTO @ClassificationID
WHILE @@FETCH_STATUS = 0
BEGIN
/* Create cursor containing all PortalIDs for first loop */
DECLARE PortalIDCursor CURSOR FAST_FORWARD FOR
SELECT PortalID
FROM Portals
WHERE PortalID IN (0,2,4,9,10,11,12,13,17,21,22,23)
OPEN PortalIDCursor
FETCH NEXT FROM PortalIDCursor INTO @PortalID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @AdminRoleID = RoleID FROM Roles WHERE PortalID = @PortalID AND RoleName='Administrators'
SET @RoleID = CASE @PermDefID
WHEN 1 THEN -5
WHEN 2 THEN -5
WHEN 3 THEN -1
WHEN 4 THEN -1
WHEN 10 THEN -1
WHEN 11 THEN -1
ELSE @AdminRoleID
END
IF NOT EXISTS (
SELECT *
FROM DNNMasters_CB_Permissions
WHERE
PermDefID=@PermDefID AND
ClassificationID=@ClassificationID AND
PortalID=@PortalID AND
RoleID=@RoleID
)
INSERT INTO DNNMasters_CB_Permissions (PermDefId, ClassificationID, PortalID, RoleID, IsAllowed)
VALUES (
@PermDefId,
@ClassificationID,
@PortalID,
@RoleID,
CASE @RoleID
WHEN -5 THEN 1
ELSE 0
END
)
FETCH NEXT FROM PortalIDCursor INTO @PortalID
END
CLOSE PortalIDCursor
DEALLOCATE PortalIDCursor
FETCH NEXT FROM ClassificationIDCursor INTO @ClassificationID
END
CLOSE ClassificationIDCursor
DEALLOCATE ClassificationIDCursor
FETCH NEXT FROM PermDefIDCursor INTO @PermDefID
END
CLOSE PermDefIDCursor
DEALLOCATE PermDefIDCursor
SET NOCOUNT OFF
|