This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over fixed database roles (db_datareader etc.)?
Fixed database roles provide permissions to the whole database. Custom roles come into play when you don't want to give users permissions to all of the database, just a portion of it.
For example, db_datareader provides SELECT permission to every table, view, etc in that database. For compliance reasons there may be some tables or views that HR staff should only be able to SELECT from and other staff shouldn't. That is usually where custom roles come into play.
Standard database roles are great for databases where there are only a few users or for when there is not a team of DBAs to manage the permissions. However, if there are several users with a large database team and sensitive data, it would be wise to create custom roles so that you know who is looking at what.
When it comes to data (and database) security, you want to lock things down as much as you can and still let everyone be able to do their jobs with no major challenges. Custom roles fits this bill quite nicely.
Practical reasons, especially with db_datareader and db_datawriter
- end users can tamper with history and audit tables
- PCI/SOX/Adit compliance
- you'll have spreadsheets connecting to your database
- ... which also gives you have unknown dependencies on table and views
- users won't understand normalisation or database design
- not all data is for all users
The other db roles aren't really used I've found