9

Entity Framework 4.1 Code First works great with SQLEXPRESS on localhost. However, I'm now ready to connect to a regular SQL 2008 server.

  1. I created a new database "NewEfDatabase".

  2. Then changed my "ApplicationServices" connectionString in Web.config to point to my new database with integrated security.

But then I get this error:

"CREATE DATABASE permission denied in database 'master'."

So...

a) What permissions does EF 4.1 CF need on said SQL server to do its work?

b) Can I setup an empty database on SQL 2008 for EF 4.1 CF, or do I have to let it do all that work for me? (I'm not sure my DBA would appreciate letting my EF app have rights to do anything outside a particular database)

Behnam Esmaili
  • 5,835
  • 6
  • 32
  • 63
Dan Sorensen
  • 11,403
  • 19
  • 67
  • 100

3 Answers3

13

Did you make sure to set your Database Initializer to null in your code:

Database.SetInitializer<MyDbContext>(null);

All built-in implementations if the initializer may try to drop or create a new database. The error you get indicate that EF tried to drop/create a database but hasn't the right to do so in your SQL Server instance. The line above is the only option to avoid this generally and is suited (I think even absolutely necessary) for live environments anyway where you don't want accidental deletion (due to model changes or something).

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • No I hadn't tried that. I'll give it a go. In that case should I migrate my data and schema from my local SQLEXPRESS? I had figured that it could build out the database in my target location. But forgot about the drop-create step it performs. – Dan Sorensen Apr 07 '11 at 16:59
  • 1
    @Dan: Yes, you must migrate the DB to the target instance if you set the initializer to `null`. In this case DB schema and EF model must match then, EF won't update or recreate the schema and probably throw exceptions if schema and model don't match. – Slauma Apr 07 '11 at 17:04
  • 1
    The way I handle this is to create a list of names of developer PCs, and I only call SetInitializer (to re-create the database) if the app is running on one of those PCs. For any other machine, I assume the database will have been deployed there already. – Ryan Lundy Apr 07 '11 at 17:54
  • I stared a new question about steps to perform to move from development to migration here: http://stackoverflow.com/questions/5585446/what-changes-are-needed-to-move-an-entity-framework-4-1-code-first-application-to – Dan Sorensen Apr 07 '11 at 17:59
  • Kyralessa: I like that concept. Thanks – Dan Sorensen Apr 07 '11 at 18:00
  • I am using database first with no initialiasers and getting this error. On my IIS Expres it works fine, then published to my local IIS it works fine, but then on the server... it throws this error? – Piotr Kula Feb 26 '15 at 11:27
3

Setup a login for your application within SQL server. Give that user dbcreator permission (by right clicking on the login, go to server roles, and check the "dbcreator" checkbox)

Schmidty
  • 1,899
  • 1
  • 20
  • 18
0

Try not to used the windows-intergrated authorization, like what I replied in this post: EF Code First - {"CREATE DATABASE permission denied in database 'master'."}. It worked for me.

Community
  • 1
  • 1
cheny
  • 2,545
  • 1
  • 24
  • 30