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.)?
Tell me more
×
Database Administrators Stack Exchange is a question and answer site for
database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
|
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. |
|||
|
|
|
No, I don't think there is a reason to favour custom database roles EXCEPT that they are more specialised and tailored to your environment and requirements. Developers will look at the standard offering, and if it fits the job - perfect! But if it doesn't, it's time to customise! |
|||
|
|
|
Practical reasons, especially with db_datareader and db_datawriter
The other db roles aren't really used I've found |
||||
|
Not the answer you're looking for? Browse other questions tagged sql-server security or ask your own question.
Community Bulletin
