LINQ to SQL Cannot create database [Schema Permissions]
c#, integration-testing, linq, linq-to-sql, sql-server-2005
Solution
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().
Problem
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.