By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SecurityRules
([ID] int, [srRight] int, [srRole] varchar(4))
;
INSERT INTO SecurityRules
([ID], [srRight], [srRole])
VALUES
(1, 4, NULL),
(2, 2, '32')
;
CREATE TABLE Projects
([ProjId] int, [prRight] int, [prRole] varchar(4))
;
INSERT INTO Projects
([ProjId], [prRight], [prRole])
VALUES
(1, 0, NULL),
(2, 0, '32'),
(3, 0, NULL)
;
5 rows affected
WITH Bits AS (
SELECT 1 AS BitMask
UNION ALL
SELECT 2 * BitMask FROM Bits
WHERE BitMask < 65536
),
NewRights AS (
SELECT ProjId, SUM(DISTINCT BitMask) AS NewRight
FROM Projects p
JOIN SecurityRules s ON s.srRole IS NULL OR s.srRole = p.prRole
JOIN Bits b ON b.BitMask & s.srRight > 0
GROUP BY ProjID
)
UPDATE p
SET p.prRight = n.NewRight
FROM Projects p
JOIN NewRights n ON n.ProjId = p.ProjId
3 rows affected
SELECT *
FROM Projects
| ProjId | prRight | prRole |
|---|---|---|
| 1 | 4 | null |
| 2 | 6 | 32 |
| 3 | 4 | null |