Steve Jones - Editor (8/12/2008)
I'd agree with Andy. The issue I have with datareader is that it automatically gives rights to all tables. So if I add a table to store anything, meta information about your database, performance, perhaps at the request of someone to store something else, everyone in that role gets rights.It means you're providing automatic access, and you might not want to. You should explicitly grant a role access if you want it, not have security setup to do the grants for you. That's the mindset that gets people into trouble.
I would respectfully disagree about datareader (though I'm wary of datawriter). While you should only use it when you know you want to person/group you are giving it to to genuinely be able to read all tables within a database including those that do not exist, there are definitely occasions when this is appropriate.
One example is if you have a developer that can be trusted with all information in the database (or if the information is not particularly confidential to begin with), they would have full rights on the development server and then datareader on the production version. Having datareader makes it easier for them to troubleshoot if there is an error or a question. This is especially relevant if the application was a rapid development project and may need to be put into in house use with less than complete testing. Of course, you could readily argue that this should not happen, but there are times when it is unavoidable.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/