8

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!

schmendrick
  • 471
  • 1
  • 5
  • 12
  • For deploying `db_ddladmin` is not an issue as you may need admin privileges to install a _plain_ desktop application. Of course that role should be revoked after DB has been created (where only `db_datareader` and eventually `db_datawriter` should be required). Of course this is more tricky (especially for automated deploying) then you may provide ah-hoc scripts that will run not under IIS user. AFAIK EF will work without that role but DB structure has to be in-place. – Adriano Repetti Dec 03 '14 at 11:28
  • 2
    You only need `db_ddladmin` for running code first migrations that actualy modify the database. If the database is up to date either because the migrations have been run already or you have turned off migrations and are keeping the DB up to date manually then `db_datareader` and `db_datawriter` are all that is required. – Ben Robinson Dec 03 '14 at 11:44
  • ok, thank you very much. I now what to do now! – schmendrick Dec 03 '14 at 14:48

1 Answers1

6

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.

Claies
  • 22,124
  • 4
  • 53
  • 77
  • 2
    You could also run the migrations separately from the main app under a different user. – Dan Jan 29 '15 at 17:53