You don’t mention what you are using to manage your database project source control, but I’m assuming Visual Studio.
If you are, then I would advocate for having the database role defined there (not it’s members, that gets messy) but the role itself. Then on each object (table, procedure, view, etc.), add commands for the permissions the role should have on it. SELECT, UPDATE, etc.
During deployment (again, assuming you publish from Visual Studio) the engine will compare the permissions you have assigned against what’s in the database and generate commands to bring the target into compliance.
Then the DBA maps windows users/groups or SQL Logins to the database role membership.
You don’t go into much detail, but even an lare number of groups to map all possible types would be ok.
Then add SYSTEM_PHI as a member of SYSTEM, etc.
Then on a table that should be used by SYSTEM_PHI only add the permissions that members of that role should able to do.
It’s not as straightforward as mapping and if you have a truly large set of categories this may be impractical, but incorporating it into source control at least carries advantages of not having to make something to do this for you, it’s about as “native” as you are going to get.
- schema compare to see deviations.
- corrections on deployments.
- source control tracking