debugsqlModerate
SQL Server: what is the reason to favor customized database roles over fixed ones?
Viewed 0 times
theoneswhatsqlfavorcustomizedrolesdatabasereasonfixed
Problem
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.)?
Solution
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.
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.
Context
StackExchange Database Administrators Q#4971, answer score: 12
Revisions (0)
No revisions yet.