Monday, December 30 2013
Keywords: EF 6, Entity Framework 6, error creating new database, cannot login error, entity framework 6 sql server error
Entity Framework "login" error: EF can modify an existing database but cannot create a new one
A principal developer came to see me the other day with a particular problem: With his Domain Driven Design Entity Framework 6 application, EF was able to alter and add objects to an existing database...but unable to create a new database if there were none. He would get a "login error" if the database did not exist...this was happening on his local SQL server were he was indeed a sysadmin...and furthermore he gave me one more clue...his colleague sitting beside him was able to do this. His colleague's EF was able to create a new database from scratch and then add all the objects to it...these two colleagues were connected to the same TFS team project, running identical code...so it wasn't a difference in the source code the problem.
I examined security and permission settings on this colleague's SQL Server (on the workstation where it wasn't working)...and compared it to his colleague's (where it was working)...and right away noticed that there was only one single difference. This had to be it...and it was.
If you suspect you are having such problem, then try this quick test:
*) Login to your SQL Server Management Studio (SSMS) using the Windows or SQL user with which you are encountering problems. When unsure it is always a good habit to take a quick glance at your SSMS's object explorer to make 100% certain you are connected with the right user. In my example I have connected with the user "ClientAB".
*) Create a new database called DatabaseFOO2.
*) If you cannot create the database and you get an error then Bingo! You probably don't have "Create Database" permission; stop reading the rest of this article...give this user sysadmin rights and then re-test your application. Otherwise, keep reading.
*) Under Security table, open Logins, and Right-Click --> "Properties" on the username in question..
*) Navigate to User Mapping. Here scroll down to databaseFOO2 that you just created. Under both 'User' as well as 'Default Schema' columns you should only see "dbo" values, that is the correct value to see. If you see the user's login name itself mentioned under "User" column, then that is the cause of the problem.
*) As for the main cause of the problem: any or all the system databases (model, msdb, master, tempdb) were selected in this list. A sysadmin does not need to be "ticked off" near any system database's check box. If you see any system database for which this user was given permissions to, make sure it to unselect it. That will fix the problem. In fact once you take these permissions off, you could create a database and you will see 'dbo' under 'User'...and your Entity Framework 6 application will work just fine...it will create the database if the database is indeed non-existent.
Written by Montreal DBA Team