Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 489
schmendrick Avatar asked Dec 03 '14 11:12

schmendrick


People also ask

What permissions does db_ddladmin have?

The db_ddladmin role gives members permissions to perform DDL operations like creating and altering tables, views, procedures, etc. It does not provide permissions to view data or assign permissions, nor does it automatically confer EXECUTE permissions on procedures or functions.

Does Entity Framework only work with SQL Server?

No. Entity Framework is a device that adapts between C# LINQ statements, and various flavors of SQL in order to download database data and transform it into a tree of bjects you can work with in C#.


1 Answers

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.

like image 118
Claies Avatar answered Oct 06 '22 07:10

Claies