8

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.)?

Improve this question

4 Answers 4

Reset to default
12

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.

Improve this answer
3

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.

Improve this answer
2

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!

Improve this answer
2

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

Improve this answer
1
  • Yes, PCI/SOX/Adit are hot topics nowadays. That may be a good enough reason to move out of fixed roles.
    – StanleyJohns
    Aug 24, 2011 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.