SQL Server Assembly (SQL CLR) 还原数据库后的问题

最近弄项目迁移的时候遇到还原数据库(SQL Server 2008)后遇到的一个问题:

消息 10314,级别 16,状态 11,第 1 行 
在尝试加载程序集 ID 65536 时 Microsoft .NET Framework 出错。服务器可能资源不足,或者不信任该程序集,因为它的 PERMISSION_SET 设置为 EXTERNAL_ACCESS 或 UNSAFE。请重新运行查询,或检查有关的文档了解如何解决程序集信任问题。有关此错误的详细信息: 
System.IO.FileLoadException: 未能加载文件或程序集“testclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null”或它的某一个依赖项。异常来自 HRESULT:0x80FC80F1 
System.IO.FileLoadException: 
   在 System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) 
   在 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) 
   在 System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) 
   在 System.Reflection.Assembly.Load(String assemblyString)

 

原因

因为您使用 Server A 上创建数据库的登录名不在 Server B 上 SQL Server 2005 实例,则会出现此问题。此登录名可能是 Microsoft Windows 登录名或 SQL Server 登录。

USE [master];
--还原数据库
RESTORE DATABASE test FROM DISK = ‘c:\test.bak‘ WITH RECOVERY,
MOVE ‘test‘ TO ‘E:\data\test.mdf‘,
MOVE ‘test_log‘ TO ‘E:\data\test.ldf‘,REPLACE;
--如果没有启用CLR,开启
EXEC sp_configure ‘clr enabled‘,1
RECONFIGURE WITH OVERRIDE;
USE test;
--查看程序集,是存在的.
SELECT * FROM sys.assemblies;
SELECT * FROM sys.assembly_files;
--还原之后的数据库TRUSTWORTHY 都是OFF的,需要重新设置
ALTER DATABASE test SET TRUSTWORTHY ON;
 USE test;
--执行存储过程
EXEC dbo.usp_test;
 但是一执行就报错了.

解决办法:

在还原数据库之后,我们可以将数据库的OWNER设置成SA.


第一步:use <databasename>

  exec sp_changedbowner ‘sa‘

第二步:alter database idmtTD set trustworthy on;

第三步:

select * from sys.databases 

select * from sys.server_principals

检验 databases表中的 owner_sid对应的表中的 sid 是否存在。主要是看数据库的所有者是否存在,在复制数据库的时候,所有者可能不存在了。

再调用存储过程就是成功的.

可以查看:KB http://support.microsoft.com/kb/918040

后来经过一些整理,发现当SQL CLR 存在EXTERNAL_ACCESS或者是UNSAFE的程序集的时候,SQL Server会检查DBO的SID在sys.databases 和sys.server_principals是否一致.

 因此我们可能未必一定要修改成sa 的,只要所有者的SID在sys.databases和sys.server_principals 是一致的,就不出问题.

我们在SSMS里面右键数据库属性->找到文件选项卡->发现在所有者(是空的,还原以后原来的SID,数据库所有者在当前的sys.server_principals不匹配的),我们可以在 [...] 里面选择一个,具有创建CREATE ASSEMLY 权限的所有者就好,我选择了B\Administrator,然后测试 CLR 存储过程,没问题,

引深:

在SQL Server 复制里面也存在类似的问题,就是我们做 "对等复制" 的时候,会出现DBO不存在,以及sp_replcmd 不存在类似的错误.其实也是因为对等复制初始化订阅是通过 RESTORE 来实现的,因此只要简单的修改数据库所有者 就好了....那么对等复制的问题也就解决了!!

参考:http://www.cnblogs.com/bhtfg538/archive/2011/01/18/1938295.html


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。