LINQ to SQL 无法创建数据库 [架构权限]
对于某些集成测试，我想使用 LINQ to SQL 删除/重新创建测试数据库。我以前工作得很好，但是在这个项目中，数据库被分成几个模式。
当我尝试运行 ctx.CreateDatabase() 命令时，出现以下异常：
指定的模式名称“xyz”或者 不存在或您没有 允许使用它。
我用于执行此操作的登录名具有 dbcreator 角色 - 它是否需要进一步的权限？当然，具有创建数据库权限的登录名也应该能够创建该数据库中包含的所有内容吗？
更新：
由于使用 LINQtoSQL 似乎没有解决此问题的方法，因此有人建议使用任何类似的工具来生成最好是免费的数据库吗？理想情况下，我不想手动编写 sql 构建脚本。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
据我所知，CreateDatabase() 方法在复制原始数据库方面受到限制。它不会重新创建触发器和检查约束之类的东西，我猜它也不会创建自定义模式。您可能想考虑使用 SQL Server .mdf 文件来解决此问题。请参阅此博客文章了解更多信息有关 CreateDatabase() 的一些限制的详细信息。
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().
我通常在 NAnt 中执行此类工作来创建、初始化数据库、创建用户、添加登录名等......以及回滚功能。如果您有兴趣，我已经就这个主题写了很多文章：
使用 NAnt 构建自动化
与 CruiseControl.NET 持续集成
我必须看看是否可以让 LINQ to SQL 按照您尝试使用的方式工作。 ..这听起来就像我们以前对 NHibernate 所做的那样。
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.
dbcreator 固定服务器角色授予您创建数据库的权限。如果您创建一个数据库，您就是该数据库的 dbo，并且作为 dbo，您在数据库中拥有绝对的权力，包括创建、更改和删除任何模式以及任何模式中包含的任何对象的权力。
LINQ 的 CreateDatabase() 的问题不是权限，而是代码质量。生成的 SQL 代码根本不创建所需的架构，因此创建表语句会失败，因为架构不存在。
如果您负担得起，最好的选择是添加 VSTS Database Edition GDR R2 项目添加到您的解决方案，并在数据库版本项目（解决方案的一部分）中声明所有数据库对象。您还将获得将所有数据库对象存储在适当的源代码控制解决方案中的额外好处。数据库项目的输出将是一个包含数据库定义的 .dbschema 文件。在部署时（测试或实际），您将运行 VSDBCMD 部署和架构导入工具 将您的 .dbschema 导入到目标服务器中。该工具能够对架构进行初始部署以及进一步升级（仅部署差异）。 VSDB 解决方案将允许您控制所有数据库对象：表、索引、视图、模式、字段约束、表约束、触发器、过程、用户、权限、登录名等。它确实涵盖了所有可以在 SQL Server 中定义的对象。
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.
实际上 LINQ to SQL 确实支持架构，但并非每个 Sql Server 版本都支持。要使
CreateDatabase()能够生成它们，DataContext必须知道目标数据库确实支持它们。可以通过在DataContext上设置提供程序来完成：Actually LINQ to SQL does support schemas, but not every Sql Server edition does. To enable
CreateDatabase()to generate them theDataContextmust be aware that the target database does support them. It can be done by setting the provider on theDataContext:您的用户还需要该数据库的
db_dlladmin。Your user also requires
db_dlladminfor that database.我肯定会关注实体框架，这些天我开始研究它。它是一个 OR/M，并且绝对会满足您的需求，并且在下一个版本发布后还会满足更多需求。
实体框架也是 Microsoft 的创意，可以在这里找到：http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
在 LINQ to SQL 和 LINQ to Entities 之间要记住的一件事是，您正在针对模型，而不是数据库。
I would definately look at Entity Framework, which I am beginning to look into these days. It's an OR/M, and will most definately suit your needs, and alot more once the next version is released.
Entity Framework is also a brain-child of Microsoft and can be found here: http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
One thing to remember between LINQ to SQL and LINQ to Entities is that you are programming against a model, and not the database.