Is there a way to provide the record creator with the access rights to it?

Hello,

To set the access rights for the record creator to be always able to view the added records, you need to run the 2 following scripts:

Script number 1 (deletes all information about records rights in the specified object and provides Authors with the default permissions):

/*
* Right level: 0 - Deny; 1 - CanRead; 2 - CanEdit
*
* Operation: 0 - Read; 1 - Edit; 2 - Delete
*/
DECLARE @TableName NVARCHAR(250)
SET @TableName = 'Account' -- SPECIFY THE TABLE NAME HERE
PRINT 'Start process ' + @TableName
EXEC ('
DELETE FROM Sys' + @TableName + 'Right;
/* Full access rights for the author of the record */
INSERT INTO Sys' + @TableName + 'Right (CreatedOn, ModifiedOn, RecordId,
    SysAdminUnitId, Operation, RightLevel, Position, SourceId)
SELECT GETUTCDATE(), GETUTCDATE(), o.Id, au.Id, op.Id, 2, 0, ''{4220CFBA-0514-44CE-ADD0-109B54B52084}'' -- Source: Author
FROM [' + @TableName + '] o
JOIN SysAdminUnit au ON (au.ContactId = o.CreatedById)
CROSS JOIN (SELECT 0 Id UNION ALL SELECT 1 UNION ALL SELECT 2) op -- Operations: Read, Edit, Delete
WHERE NOT EXISTS(SELECT * FROM Sys' + @TableName + 'Right WHERE RecordId = o.Id AND
    SysAdminUnitId = au.Id AND Operation = op.Id);
/* Copying default permissions */
INSERT INTO Sys' + @TableName + 'Right (CreatedOn, ModifiedOn, RecordId,
    SysAdminUnitId, Operation, RightLevel, Position, SourceId)
SELECT DISTINCT GETUTCDATE(), GETUTCDATE(), o.Id, dr.GranteeSysAdminUnitId,
    dr.Operation, dr.RightLevel, 0, ''{F41E0268-E324-4228-9E9E-5CB7CC906398}'' -- Source: Default rights
FROM [' + @TableName + '] o
JOIN SysAdminUnit au ON (au.ContactId = o.CreatedById)
LEFT JOIN SysUserInRole uir ON (uir.SysUserId = au.Id)
JOIN SysEntitySchemaRecordDefRight dr ON (dr.AuthorSysAdminUnitId = uir.SysRoleId OR dr.AuthorSysAdminUnitId = au.Id OR
dr.AuthorSysAdminUnitId IN (SELECT Id FROM SysAdminUnit WHERE ParentRoleId IS NULL))
WHERE dr.SubjectSchemaUId IN
    (SELECT UId FROM SysSchema WHERE [Name] = ''' + @TableName + ''') AND
    NOT EXISTS(SELECT * FROM Sys' + @TableName + 'Right WHERE RecordId = o.Id AND
        SysAdminUnitId = dr.GranteeSysAdminUnitId AND Operation = dr.Operation);
')
PRINT @TableName + ' was processed'

Script number 2 (inserts permissions for Owners of the records in the specified object):

/*
* Right level: 0 - Deny; 1 - CanRead; 2 - CanEdit
*
* Operation: 0 - Read; 1 - Edit; 2 - Delete
*/
DECLARE @TableName NVARCHAR(250)
SET @TableName = 'Account' -- SPECIFY THE TABLE NAME HERE
PRINT 'Start process ' + @TableName
EXEC ('
/* Full access rights for the Owner of the record */
INSERT INTO Sys' + @TableName + 'Right (CreatedOn, ModifiedOn, RecordId,
    SysAdminUnitId, Operation, RightLevel, Position, SourceId)
SELECT GETUTCDATE(), GETUTCDATE(), o.Id, au.Id, op.Id, 2, 0, ''{4220CFBA-0514-44CE-ADD0-109B54B52084}'' -- Source: Author
FROM [' + @TableName + '] o
JOIN SysAdminUnit au ON (au.ContactId = o.OwnerId)
CROSS JOIN (SELECT 0 Id UNION ALL SELECT 1 UNION ALL SELECT 2) op -- Operations: Read, Edit, Delete
WHERE NOT EXISTS(SELECT * FROM Sys' + @TableName + 'Right WHERE RecordId = o.Id AND
    SysAdminUnitId = au.Id AND Operation = op.Id);
/* Copying default permissions */
INSERT INTO Sys' + @TableName + 'Right (CreatedOn, ModifiedOn, RecordId,
    SysAdminUnitId, Operation, RightLevel, Position, SourceId)
SELECT DISTINCT GETUTCDATE(), GETUTCDATE(), o.Id, dr.GranteeSysAdminUnitId,
    dr.Operation, dr.RightLevel, 0, ''{F41E0268-E324-4228-9E9E-5CB7CC906398}'' -- Source: Default rights
FROM [' + @TableName + '] o
JOIN SysAdminUnit au ON (au.ContactId = o.OwnerId)
LEFT JOIN SysUserInRole uir ON (uir.SysUserId = au.Id)
JOIN SysEntitySchemaRecordDefRight dr ON (dr.AuthorSysAdminUnitId = uir.SysRoleId OR dr.AuthorSysAdminUnitId = au.Id OR
dr.AuthorSysAdminUnitId IN (SELECT Id FROM SysAdminUnit WHERE ParentRoleId IS NULL))
WHERE dr.SubjectSchemaUId IN
    (SELECT UId FROM SysSchema WHERE [Name] = ''' + @TableName + ''') AND
    NOT EXISTS(SELECT * FROM Sys' + @TableName + 'Right WHERE RecordId = o.Id AND
        SysAdminUnitId = dr.GranteeSysAdminUnitId AND Operation = dr.Operation);
')
PRINT @TableName + ' was processed'

Best regards,

Lisa