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

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

消息 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)

　　至于产生的原因大概是,在备份数据库的时候,在机器A,那么数据库的拥有者是AAdministrator(如果用windows登录创建),那么但是我们还原到服务器B,那么拥有者可能是BAdministrator,那么SQL CLR的安全性会认为该程序集不可靠.

　　例如:

　　我首先创建一个简单的SQL CLR 存储过程:

using System;using System.Data;  
　　using System.Data.SqlClient;  
　　using System.Data.SqlTypes;  
　　using Microsoft.SqlServer.Server;    
　　public partial class StoredProcedures{   
　　 [Microsoft.SqlServer.Server.SqlProcedure]      
　　publicstaticvoid Test()    {        

　　// 在此处放置代码    }};编译创建程序集.

　　在机器A上的数据库上执行:

CREATE DATABASE test;   
　　USE test;   
　　ALTER DATABASE test SET TRUSTWORTHY ON;   
　　CREATE ASSEMBLY [TestCLR] FROM 'E:\Documents\Visual Studio 2010\Projects\TestCLR\TestCLR\bin\Release\TestCLR.dll'
　　WITH PERMISSION_SET = EXTERNAL_ACCESS;  
　　--SAFE;  
　　CREATE PROC dbo.usp_testASEXTERNAL NAME [TestCLR].StoredProcedures.Test;   
　　EXEC dbo.usp_test;   
　　USE master;   
　　BACKUP DATABASE test TO DISK = 'c:\test.bak' WITH FORMAT;

　　将c:test.bak copy 到机器B上,然后执行:

　　USE [master];--还原数据库RESTORE DATABASE test FROM DISK = 'c:test.bak' WITH RECOVERY,MOVE 'test' TO 'E:datatest.mdf',MOVE 'test_log' TO 'E:datatest.ldf',REPLACE;

　　--如果没有启用CLR,开启EXEC sp_configure 'clr enabled',1RECONFIGURE 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.

exec sp_changedbowner 'sa'

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

　　可以查看:KB

　　后来经过一些整理,发现当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 权限的所有者就好,我选择了BAdministrator,然后测试 CLR 存储过程,没问题,

　　引深:

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