Database Fixed Roles 2

db_ddladmin

The db_ddladmin is another powerful role because it allows a user to create, drop, or modify any objects within a database, regardless of who owns it. So a user could alter a stored procedure owned by dbo, for instance. This role is sometimes given to developers on non-production systems as they built custom applications. However, there is typically no reason anyone should be a member of this role on a production database. One thing the db_ddladmin does not do is allow the user to alter permissions on the objects. So a member of this role can create or modify the object, such as a stored procedure, but not alter the permissions on it unless he or she is the owner.  So, for instance, a member of this role could create a stored procedure in a schema owned by dbo, but couldn't grant the ability to execute it.

• The db_ddladmin role can create, drop, and alter objects within the database, regardless of who the owner is.
• The db_ddladmin role cannot alter security.
• It is not unusual to grant this role to developers in a non-production environment.
• Normal users should not be a member of this role.
• Applications should not need this role.
• No one should normally be a member of this role on a production database.

db_datareader

The db_datareader role allows a user to be able to issue a SELECT statement against all tables and views in the database. DENY for a user (or a role the user is a member of) will still block the SELECT, however. But if there are no permissions set, whatsoever, the user will have the ability to SELECT against the table or view. The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you'll have to query the membership of this group via the use of sp_helprolemember:

EXEC sp_helprolemember 'db_datareader';

It is not unusual to see the db_datareader role used in databases. It's an easy way to grant SELECT permissions to everything without having to worry about it. However, due to the fact that it uses implicit permissions, I prefer to create a user-defined database role and explicitly grant permissions. With that said, here are things to remember:

• The db_datareader role gives implicit access to SELECT against all tables and views in a database.
• In SQL Server 2005 and up, an explicit DENY will block access to objects.
• It is not unusual to see this role used in production for developers.
• It is not unusual to see this role used in production for normal users.
• Applications will occasionally need this role.
• Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_datawriter

The db_datawriter role is like the db_datareader role in that it gives implicit access to tables and views within a database. It also can be blocked by an explicit DENY for the user or for a role the user is a member of. Unlike db_datareader, however, db_datawriter gives INSERT, UPDATE, and DELETE permissions . Again, since the permission is implicit, you will not see these rights show up in sys.database_permissions. And like with db_datareader, you'll have to check the membership of this role to determine actual permissions in the event of an audit.

• The db_datawriter role gives implicit access to INSERT, UPDATE, and DELETE against all tables and views in a database.
• In SQL Server 2005 and up, an explicit DENY will block access to objects.
• Typically developer are not members of this role in production unless all users are.
• While less common than with db_datareader, it is not all that unusual to see this role used in production for normal users.
• Applications will occasionally need this role.
• Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_denydatareader

Unlike the previous two roles, db_denydatareader denies access. In this case, the db_denydatareader is the same as having a DENY for SELECT on all tables and views in the database. Because DENY trumps everything else, this is not a role I've seen used frequently. If there are no permissions for a given user on an object, such as the user has no SELECT permissions on a table, then SQL Server blocks access. Therefore, if a user doesn't have SELECT permission on TableA, then the user cannot successfully issue a SELECT query against TableA. An explicit DENY is not needed. And since this affects all tables and views, that adds to the reason this database role is typically not used. And like db_datareader and db_datawriter, the DENY is implicit, meaning you'll have to query for membership in this role to determine who is affected.

• The db_denydatareader role is denied access to SELECT against any table or view in the database.
• Typically this role is not used.
• The DENY is implicit.
• Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.

db_denydatawriter

Wrapping up our list of roles is db_denydatawriter. The db_denydatawriter has an implicit DENY on INSERT, UPDATE, and DELETE for all tables and views in the database. Again, this is not a role that sees much use, for the same reasons as db_denydatareader.

• The db_denydatawriter role is denied access to INSERT, UPDATE, or DELETE against all tables and views in the database.
• Typically this role is not used.
• The DENY is implicit.
• Creating a user-defined database role and explicitly defining permissions is still preferred over the use of this role.