sql server – Partial role and permission based database design

The current client database has dbo.Users Table and dbo.UserRoles table. dbo.Roles is a master table holding various role description. The image below captures what they have. They are looking to expand by giving specific users with certain roles additional permission/responsibility.

The FK on UserRoles table is

CONSTRAINT (PK_dbo.UserRoles) PRIMARY KEY CLUSTERED 
(
    (UserId) ASC,
    (RoleId) ASC
)

Current Structure

The expansion of role – an example would be
Expansion example in col J

Current option

  1. Add a new column to dbo.UserRoles which I could use to store value based on bitwise operation. Refer to for example
  2. Business layer would determine and drive the UI based on what permission is enabled. This would be on user management screen where they would see additional options per user/role combo.

Con
If a lead user requires approval from manager for purchase approval – then the above schema does not support it and I will have to add a new table to support this.

Alternate option
Create a new table and use bitwise operation to store different permission per user. This only gets populated based on roles that have additional permission. This would be managed by Role-Permission table. A bit of approach taken from here.

Is there a cleaner approach to this without redoing the entire db?