As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

What is the difference between db_datawriter and db_ddladmin?

The roles db_datawriter and db_ddladmin are two of the database roles that are available on SQL Server.  Within the database, db_datawriter allows users with the role to insert, update, or delete data in any user table or view in a database.  The db_ddladmin role provides permissions for the user to modify any the schema of a database with Data Definition Language commands.  The permissions for these two roles do not overlap.

When it comes to leveraging these roles, there are differences in their use.  To start, db_datawriter is a role that wouldn’t be uncommon to see granted to users of a database.  It provides a simple, but possible excessive, method for granting insert, update, and delete statements for users and application.  With db_ddladmin, on the other hand, there are much fewer use cases.  This role should not be granted to users or applications.  While there are few scenarios where the role should be granted to users, it is ideal for logins that are intended to deploy code.

Summary

This is a fairly straight up question with a straight up answer.  My question for those reading this, when do you use these database roles?  What are some scenarios where you’ve used one or the other role?