Hi Joe,
If you're using MakeFolder the permissions are inherited by default. In the next version there'll also be a SPROC to create a folder including read/write role settings. Here it is:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}DMX_Split') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION {databaseOwner}{objectQualifier}DMX_Split
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}DMX_Split (@s varchar(512), @sep char(1))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DMX_AddFolder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddFolder
@PortalId INT,
@CollectionId INT=0,
@FolderName NVARCHAR(255)='New Folder',
@ViewRoles VARCHAR(2000)='',
@EditRoles VARCHAR(2000)='',
@AddRoles VARCHAR(2000)='',
@ApproveRoles VARCHAR(2000)=''
AS
BEGIN
DECLARE @Owner INT
SET @Owner = (SELECT AdministratorId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
DECLARE @NewId INT
INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries
(CollectionId, Created, Deleted, Entry, EntryType, IsApproved, Owner, PortalId, Version, PermissionsOnlyByAdmin)
VALUES
(@CollectionId, GETDATE(), 0, '', 'Collection', 1, @Owner, @PortalId, 1, 0);
SET @NewId = SCOPE_IDENTITY()
UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET LastVersionId=EntryId
WHERE EntryId=@NewId;
INSERT INTO {databaseOwner}{objectQualifier}DMX_ShortTexts
(Locale, ObjectId, SubTypeKey, [Text], TypeKey)
VALUES ('', @NewId, 'TITLE', @FolderName, 'ENT');
INSERT INTO {databaseOwner}{objectQualifier}DMX_Log
([Action],[Datime],[EntId],[UserId])
VALUES ('Add', GETDATE(), @NewId, @Owner);
DECLARE @AdminRoleId INT
SET @AdminRoleId = (SELECT AdministratorRoleId FROM {databaseOwner}{objectQualifier}Portals WHERE PortalId=@PortalId)
IF @ViewRoles='' AND @EditRoles='' AND @AddRoles='' AND @ApproveRoles=''
BEGIN
IF @CollectionId=0
BEGIN
-- set Admin roles for default folder under root
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, PermissionId, @PortalId, @AdminRoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Permissions
WHERE PortalId=@PortalId AND (PermissionKey='VIEW' OR PermissionKey='EDIT' OR PermissionKey='ADD');
END
ELSE
-- Inherit permissions from parent
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, [PermissionId],[PortalId],[RoleId],[UserId]
FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions
WHERE EntryId=@CollectionId;
END
ELSE
BEGIN
-- set View roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@ViewRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='VIEW')
WHERE NOT @ViewRoles='';
-- set Eit roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@EditRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='EDIT')
WHERE NOT @EditRoles='';
-- set Add roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@AddRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='ADD')
WHERE NOT @AddRoles='';
-- set Approve roles
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, p1.PermissionId, @PortalId, r1.RoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Split(@ApproveRoles, ';') spl1
INNER JOIN {databaseOwner}{objectQualifier}Roles r1 ON (CAST(r1.RoleId AS VARCHAR(10))=spl1.s OR r1.Rolename=spl1.s)
INNER JOIN {databaseOwner}{objectQualifier}DMX_Permissions p1 ON (p1.PortalId=@PortalId AND p1.PermissionKey='APPROVE')
WHERE NOT @ApproveRoles='';
-- make sure Admin is part of this
INSERT INTO {databaseOwner}{objectQualifier}DMX_EntryPermissions
([AllowAccess],[EntryId],[PermissionId],[PortalId],[RoleId],[UserId])
SELECT 1, @NewId, p.PermissionId, @PortalId, @AdminRoleId, -10
FROM {databaseOwner}{objectQualifier}DMX_Permissions p
WHERE p.PortalId=@PortalId AND (p.PermissionKey='VIEW' OR p.PermissionKey='EDIT' OR p.PermissionKey='ADD')
AND NOT EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}DMX_EntryPermissions ep
WHERE ep.PermissionId=p.PermissionId AND ep.EntryId=@NewId AND ep.RoleId=@AdminRoleId);
END
END
GO
You can use this SPROC to add a folder (note it uses the split function above it). Use semi-colon separated list of role names or ids.
After you've created the folder any content underneath will inherit the roles. Note that folder to file inheritance moves ADD to EDIT roles.
Peter