Entity Framework migrations database role

Question

I am using Entity Framework migrations in a SQL Azure database. I would like to know what database role will allow EF to do everything it needs to do without having the ability to create a database or drop a database.

It seems that db_owner has the ability to drop a database so I do not want to add a user to that role. db_datawriter doesn't seem to be able to able to create / delete tables, which would be necessary when adding new entities to the migrations.

Is there another role I could use?

Answer 1

Have you tried the db_ddladmin role? This role can run any DDL command in the database (e.g. schema changes), but can't create or drop the database.

In addition, as you note, the db_datawriter role can be used to allow changes to the data in your tables (e.g. if your migrations are seeding data).

More info: https://msdn.microsoft.com/library/ms189121.aspx