How to: Inherit Database Role permissions

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'RestrictedRole')
exec sp_addrole 'RestrictedRole', 'dbo';

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'LessRestrictedRole')
exec sp_addrole 'LessRestrictedRole', 'dbo';


GRANT SELECT, INSERT, UPDATE on Table1 to RestrictedRole;
GRANT SELECT on Table2 to RestrictedRole;

EXEC sp_addrolemember 'RestrictedRole', 'LessRestrictedRole';

GRANT INSERT, UPDATE on Table2 to LessRestrictedRole;

The sp_addrolemember makes LessRestrictedRole a member of RestrictedRole, which means that LessRestrictedRole inherits all permissions given to RestrictedRole.

Granting more permissions to LessRestrictedRole would make those additional permissions exclusive to LessRestrictedRole - LessRestrictedRole may Insert and Update rows in Table2 while RestrictedRole may only Select rows.

By virtue of Inhertance, both LessRestrictedRole and RestrictedRole may Select, Insert and Update Table1.