Can you create a custom DB_role like truncate, select, insert, delete permission to tables that are stored in another table(like lookup) rather than altering each table? just wondering I’m not a DBA sorry, the reason why is the old database has only one schema.
the only thing I can think of is creating an SP and do a loop on that table.
Roles need to be explicitly defined. You can’t tell the engine to just look up the role membership and/or rights in a table.
You’ll need to manually create the roles/permissions with dynamic SQL from the table, and come up with some kind of plan to keep them in sync whenever the data in the table changes (you can use a trigger or, if a delay is possible, a scheduled job).
You mention schemas, and I’m not sure how relevant that is, but obviously an easier way to manage this would be to put all of the relevant users in a role, put all the relevant objects in a schema, and
GRANT permissions on the schema to the role. If you have exceptions you can implement