I'm redesigning security setup in our database. I was in process of defining certain user-defined roles and - as I find it handy - on the other monitor I was browsing documentation to make sure I don't miss anything.
And in this resource from the Microsoft Docs I noticed the following:
Do not add user-defined database roles as members of fixed roles. This could enable unintended privilege escalation.
Now - either I'm tired or there is no good explanation for this tip.
In fact I find it common to define specific role and ensure they have proper permission through fixed role membership.
- "power user" = db_datareader + db_datawriter
- "developer" = db_datareader + db_datawriter + ddladmin
- "function designer" = db_datareader + GRANT CREATE FUNCTION on database:xyz
It's just handy.
I'm putting this security setup on hold - hopefully someone can give me some good pointers here.