LINQ to SQL Cannot create database [Schema Permissions]

Question

For some integration tests I want to use LINQ to SQL to drop/re-create the test database. I've had this working fine before, however in this project the database is split up into several schemas.

When I try to run the ctx.CreateDatabase() command I'm getting this exception:

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

The login I'm using to do this has the role dbcreator - Does it need further permissions? Surely a login with persmissions to create a database should be able to create everything contained in that database also?

Update:

Since it looks like there isn't a solution to this problem using LINQtoSQL, does anyone have recommendations of any similiar tools to generate a db that are preferably free? Ideally I don't want to have to muck about hand writing sql build scripts.

Answer 1

From what I've read, the CreateDatabase() method is limited in what it can reproduce of the original database. It won't recreate things like triggers and check constraints, and I'm guessing it doesn't create custom schemas either. You may want to look into creating the database using a SQL Server .mdf file instead to work around this issue. See this blog entry for more details on some of the limitations of CreateDatabase().

Answer 2

The dbcreator fixed server role grants you the permission to create a database. If you create a database, you are the dbo of said database and as dbo you have absolute power in the database, includding the power to create, alter and drop any schema and any object contained in any schema.

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

Your best choice, if you can afford it, is to add a VSTS Database Edition GDR R2 project to your solution and declare all your database objects in the Database Edition project (part of your solution). You'll be also getting the added benefit of storing all your database objects in a proper source control solution. The output of the Database project would be a .dbschema file containing the definition of your database. At deployment time (test or real) you would run the VSDBCMD Deployment and Schema Import tool to import your .dbschema into the target server. The tool is capable of doing initial deployment of your schema, as well as further upgrades (deploy only differences). The VSDB solution would allow you to controll all your database objects: tables, indexes, views, schemas, field contraints, table constraints, triggers, procedures, users, permissions, logins etc etc. It really covers all the objects that can be defined in SQL Server.

Answer 3

I generally do this sort of work in NAnt to create, initialize the database, create users, add logins, etc....and also roll back capabilities. I have written on this topic quite a bit if you are interested:

Build automation with NAnt

Continuous integration with CruiseControl.NET

I will have to see if I can get LINQ to SQL to work in the way you are trying to use it...that sounds like what we used to do with NHibernate.