Do I need SQL Server db_ddladmin rights for a Entity Framework Code-First Approach and is it a security issue?

Question

we have an external project that has been developed using C# & Entity Framework 6 Code First together with SQL Server 2014. It has a web site and a wcf service.

Now, for deployment the project-contractor stated that the IIS APPOOL user under which the web site runs will need membership to db_datareader, db_datawriter and db_ddladmin for it to work and indeed without giving those rights it did not work.

I have certain problems with that because without being a SQL-Guru I feel that a deployment should not need db_ddladmin rights while the contractor says thats perfectly normal with EF Code First and does not see a problem. A search on the net also seems to reveal that those rights can be problematic (http://akawn.com/blog/2012/02/why-you-should-be-cautious-with-the-dbo_owner-role/) which would also indicate to me that it would be a bit insane that EF framework Code First really would need those rights...

So do I need db_ddladmin for EF Code First? And is it problematic that a deployed web project needs database access rights of db_ddladmin?

Thanks in advance!

Answer 1

This is indeed a possible pain point. Entity Framework does assume that you have db_ddladmin rights for migrations. It does not require this permission at any other stage.

There are a few ways to handle this. You can either give the permissions, run the migrations, then remove the permission, or you can export a SQL script and run it against your server, like so: Update-Database -Script -SourceMigration:0 (script from beginning to current state). Any future migration deployments would require you to take the same steps, starting from the server's current migration value, or adding/removing the permission.