4

I am developing with Entity Framework code first and SQL Server 2008.

I want to support this common installation scenario:

  1. Database administrator manually creates a new database and a new user that has access only to that database (database_owner permission)
  2. My application is configured with those database details and the new user's login
  3. My application uses Entity Framework to automatically initialize the database (create the tables, views, etc)

However, when I try to get Entity Framework to initialize the database:

context.Database.Create();

I will get an exception, because this method tries to create the database, not just create the tables, views, etc inside the database.

How can I get Entity Framework to initialize a database with a user that does not have "create database" permissions, using a database that has already been created for it?

Joe Daley
  • 45,356
  • 15
  • 65
  • 64
Clover
  • 266
  • 1
  • 4
  • 14
  • 1
    Your question is confusing. If you only want the user to have create table permissions, why do you want them to be able to create a database? For them to create a database first, you need to give them permissions at the SQL Server login level, not the database user level. Have you applied any roles or permissions to the SQL Server login? Is it really your intention to give them the permission to create a database? How will you make sure they only create one? How many databases do you want to end up with on your server? Usually you create a database and then let users create objects in that db. – Aaron Bertrand Jul 11 '11 at 02:52
  • In brief: **NOT**. If you don't have the permission to `CREATE DATABASE` - you **cannot** under any circumstances create a database - that's the whole point of that permission...... – marc_s Jul 11 '11 at 07:21
  • 1
    This question is not about database security. It is about Entity Framework's annoying insistence on being given create database permissions even if all you want it to do is create the tables. I have submitted an edit to hopefully clarify the question. – Joe Daley Oct 29 '11 at 04:46

2 Answers2

4

There is an implementation of IDatabaseIntializer that only creates the tables:

http://nuget.org/List/Packages/EFCodeFirst.CreateTablesOnly

There are also good solutions here:

Entity Framework CTP 4 - Code First Custom Database Initializer

Entity Framework 4 Code First - Prevent DB Drop/Create

Community
  • 1
  • 1
Joe Daley
  • 45,356
  • 15
  • 65
  • 64
3

Irrelevant of the technology being used, application should separate deployment privileges from normal use privileges. Is perfectly acceptable to require higher privileges for deployment and you should follow the same model. In other words your deployment (read: 'Setup' or 'MSI' or 'Installation') should require higher privileges (eg. dbcreator membership) and, under this elevated context of deployment, create the 'code first' model. So simply move the 'context.Database.Create' into your application Setup and have an administrator install the application.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Good answer. How do we do this if it is a web application? I guess we should apply the same principle, but how do we do that? – Krishnan Sep 05 '19 at 09:08