LINQ to SQL Cannot create database [Schema Permissions]

Question

For some integration tests, I want to use LINQ to SQL to dump / recreate a test database. I used to work on this, but in this project the database is divided into several schemes.

When I try to run the ctx.CreateDatabase() command, I get this exception:

The specified schema name "xyz" either does not exist or you do not have permission to use it.

The input I use for this has the role of dbcreator - Do I need additional permissions? Of course, a login with intercepts to create a database should also create everything that is contained in this database?

Update:

Since it seems like this problem is not being used using LINQtoSQL, does anyone have any recommendations for any similar db generation tools that are preferably free? Ideally, I do not want you to write about writing sql build scripts.

Answer 1

From what I read, the CreateDatabase () method is limited by what it can reproduce in the source database. It will not recreate things like triggers and control constraints, and I assume that it does not create custom circuits either. You might want to explore database creation using SQL Server.mdf to work around this problem. See this blog post for more information on some of the limitations of CreateDatabase ().

Answer 2

I usually do this kind of work in NAnt to create, initialize a database, create users, add logins, etc., and also rollback. I wrote a little on this subject if you are interested:

Build Automation with NAnt

Continuous Integration with CruiseControl.NET

I will need to see if I can make LINQ to SQL work the way you are trying to use it ... this is similar to what we did with NHibernate.

Answer 3

The dbcreator fixed server role grants you permission to create a database. If you create a database, you are a dbo of the specified database and like dbo, you have absolute power in the database, including the ability to create, modify and delete any circuit and any object contained in any circuit.

the problem with LINQ CreateDatabase () is not permission, it is code quality. The generated SQL code simply does not create the necessary schema, so the Create table statements fail because the schema does not exist.

Your best bet, if you can afford it, is to add the VSTS Database Edition GDR R2 to your solution and declare all your database objects in the Database Edition project (part of your solution). You will also get the added benefit of storing all database objects in the correct version control solution. The result of the database project is a .dbschema file containing the definition of your database. During deployment (test or real), you run the VSDBCMD Schema Import and Import Tool to import your .dbschema to the target server. The tool is able to carry out the initial deployment of your schema, as well as further updates (deploy only the differences). The VSDB solution allows you to control all your database objects: tables, indexes, views, schemas, field restrictions, table restrictions, triggers, procedures, users, permissions, logins, etc. It really covers all the objects that can be defined in SQL Server.

Answer 4

In fact, LINQ to SQL supports schemas, but not every version of Sql Server. To enable CreateDatabase() to create them, the DataContext must know that the target database supports them. This can be done by installing the provider on the DataContext :

 [Provider(typeof(Sql2008Provider))] public class CustomDataContext : DataContext { ... } 

Answer 5

The user also requires db_dlladmin for this database.

Answer 6

I would definitely look at the Entity Framework with which I am starting to look these days. This is OR / M and will most definitely suit your needs, and many more times when the next version will be released.

Entity Framework is also the brain of Microsoft's child and can be found here: http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx

One thing to keep in mind between LINQ to SQL and LINQ to Entities is that you are programming against a model, not a database.