I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database. I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level.
But that got me thinking, what is the difference (if any) between granting a user SELECT permission at a database level, or adding them to the db_datareader role? And is there any best practice one way or the other?
Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx
The chart shows that db_datareader role is identical to GRANT SELECT ON [database]. So it is still fine to use, but the recommendation is to move away from those roles to the more granular commands. Some of the other fixed database roles are less clearly defined for most people. Using the explicit commands results in greater clarity when reporting rights.
Obviously you know how to grant finer grained permissions. I am trying to break loose from the old roles whenever possible, but db_owner (for example) is a hard habit to break.
