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.
Simplified examples:
- "power user" = db_datareader + db_datawriter
- "developer" = db_datareader + db_datawriter + ddladmin
- "function designer" = db_datareader + GRANT CREATE FUNCTION on database:xyz
etc.
It's just handy.
I'm putting this security setup on hold – hopefully someone can give me some good pointers here.
Best Answer
The author is recommending that you practice the Principle of Least Privilege.
Basically, the author is saying that if you create a Role, it should do exactly what you define that role to do and nothing else at all. If you then assign it to a built-in role, you're now greatly expanding the role's permissions and losing some control over exactly what those permissions might be.