naveenkumar_segu (1/13/2011)
db_datareader -> grants select permissions and doesn't allow you to update anything in the tables (means denying insert, update and delete permissions)
db_datareader doesn't deny you insert, update, delete permissions. It simply doesn't grant them.
I feel both roles do the same job. isn't it?
No, not at all.
db_datareader grants select permissions on all tables. Nothing more. It does not affect any insert, update, delete permissions at all, if a user has those from somewhere else, they can insert, update and delete.
db_denydatawriter denies permission to do any changes to any table. Even if someone was granted insert permissions directly they would still not be able to insert, because deny overrules grant. Assigning a user to this role means that they will never be able to make any changes to the DB, regardless of what other permissions they have.
This does not grant select permissions, but it also does not deny select permissions.
Please do some reading on the differences between GRANT, REVOKE and DENY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability