Sql-server – what is the difference between db_datareader and db_denydatawriter

The system privileges that relate to the object types (CREATE TABLE, VIEW, ...) can be divided in two groups:

• Those that lack the keyword ANY are only effective on the user's own schema.
• Those with the keyword ANY (DROP ANY TABLE, EXECUTE ANY PROCEDURE, ...) are effective on all schemas. Consequently, you should restrict the ANY privileges to administrative users.

If you grant an object type privilege (without ANY) such as CREATE TABLE to a role, all users of this role will be able to assert this privilege on their own schema. Consequently you can grant these privileges to a role and the users will inherit such rights (on their own schema) when they are granted the role.

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.