We have table name AreaMapping and AreaPermission.
Below SQL query I aggregated for entitlement. Now the issue I am facing is like i have raised the request for Areacode_readonly in database it got updated with areacode and securitylevel. Again I raised the request for Areacode_DataEntry then Areacode remain same and securitylevel got updated to 20. Now again I am raising the request for Areacode_Readonly then I receive the below error.
Already has access to this item, only access remove date can be modified.
Any idea how to provide Granular access to user based on Areacode and securitylevel.
SELECT
a.AreaCode,
s.SecurityLevel,
a.AreaName,
CONCAT(
a.AreaCode, '-',
CASE
WHEN s.SecurityLevel = 10 THEN 'ReadOnly'
WHEN s.SecurityLevel = 20 THEN 'DataEntry'
WHEN s.SecurityLevel = 30 THEN 'AdvancedUser'
ELSE 'Unknown'
END
) AS AreaCode_SecurityLevel
FROM [DummyDB].[dbo].[AreaMapping] a
INNER JOIN [DummyDB].[dbo].[UserPermissions] s
ON s.AreaId = a.AreaId
WHERE a.IsActive = ‘Y’;