Locating Document IDs and Removing/Adding Permissions, by script

The scripts below will locate all documents in a folder, and subfolders, and allow you to delete and add permissions for these document folders.

This is a script to get all ItemIDs of documents in folder, including all subfolders all the way down. These scripts are useful if you have 2800 documents in a folder and need to set permissions and the portal times out while doing so.

With MyCTE

AS (SELECT V.ItemID, V.ParentID, 1 as DocLevel

FROM [Version] as V where V.ParentID is Null and V.ItemID = ##
UNION ALL
Select W.ItemID, W.ParentID, MyCTE.DocLevel + 1 from [Version] as W
INNER JOIN MyCTE on W.ParentID = MyCTE.ItemID WHERE W.ParentID is not null
)
Select DISTINCT ItemID from MyCTE

With this script, you can do fun things like, delete all users permissions from the folder and all files underneath it:

With MyCTE
AS (SELECT V.ItemID, V.ParentID, 1 as DocLevel

FROM [Version] as V where V.ParentID is Null and V.ItemID = ##
UNION ALL
Select W.ItemID, W.ParentID, MyCTE.DocLevel + 1 from [Version] as W
INNER JOIN MyCTE on W.ParentID = MyCTE.ItemID WHERE W.ParentID is not null
)
Delete from PermissionsByUser where ItemID in (Select Distinct ItemID from MyCTE)

You can then add permissions for all users back into the portal for that folder and all items underneath it:

Note: The 1s can be changed to 0s if you do not want them to be a reader or writer.

With MyCTE
AS (SELECT V.ItemID, V.ParentID, 1 as DocLevel

FROM [Version] as V where V.ParentID is Null and V.ItemID = 75
UNION ALL
Select W.ItemID, W.ParentID, MyCTE.DocLevel + 1 from [Version] as W
INNER JOIN MyCTE on W.ParentID = MyCTE.ItemID WHERE W.ParentID is not null
)
Insert INTO PermissionsByUser
Select i.ItemID, u.UserID, '1' as isReader, '1' as isWriter from (Select Distinct ItemID from MyCTE) i CROSS JOIN (Select UserID from Production_Portal_PortalFx.dbo.PWUser where Activated = 1) u