Take the 2-minute tour ×
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.

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

share|improve this question

4 Answers 4

up vote 11 down vote accepted

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.

share|improve this answer

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!

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
Yes, PCI/SOX/Adit are hot topics nowadays. That may be a good enough reason to move out of fixed roles. –  StanleyJohns Aug 24 '11 at 18:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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