none
How to use non-default schema name for SQL Server? RRS feed

  • Question

  • I have a database in SQL Server 2012 and I created an EF project that used the default schema name of dbo by using the windows authentication connection. This worked fine.

    Then I created a another schema in the same SQL SERVER database used in the first project called PAZ and an accompanying user and login for which PAZ is the default.

    Then I created a second Visual Studio EF project and EDMX. Since it was using the connection string for the SQL authentication the user/login for which PAZ was the default schema, I expected the generated ".sql" would use "PAZ" instead of "dbo" everywhere.

    It did not!Why not?

    So I did a global search and replace on the sql file and executed the SQL file with the green triangle in Visual Studio. However, the C# code throws an exception when I try to save an entity -- probably because the C# code is erroneously trying to save my entity to a table called dbo.User when the Visual Studio's connection string does not grant it permission to save to that table.

    (1) Why did not the SQL file generated from the diagram examine the connection string and connect to SQL Server and see that I was using the new PAZ schema instead of the old dbo schema?

    (2) How do I make it (visual studio and my generated ORM) use the PAZ schema?

    Thanks

    Siegfried


    siegfried heintze


    • Edited by siegfried_ Saturday, April 27, 2013 10:53 PM
    Saturday, April 27, 2013 10:49 PM

Answers

  • Hi siegfried_,

    I suppose you are using Model First in the second project to create models and generate database from model, am I right?

    When using Model First, dbo is the default schema name. However we can change the database schema name in the property window of the model by right click Property in the EDM designer or model in Model Browser. There is a Database Schema Name property. If we change this property, the schema name will be changed in generated DDL.

    If there are some other complicated requirements, we have to create a new T4 template to generate DDL. The default template is located in \Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt. After we have added new template in the same folder, we can select the template in the drop down of DDL Generation Template property.

    http://msdn.microsoft.com/en-us/data/ff830362.aspx

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by siegfried_ Thursday, May 2, 2013 4:13 AM
    Wednesday, May 1, 2013 6:48 AM
    Moderator