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

数据库 SQL Server
今天我们将介绍在SQL Server Assembly还原数据库中,如果备份的数据库存在EXTERNAL_ACCESS 和UNSAFE的程序集,那么在还原的时候程序集会出现一些奇怪的错误。

前端时间给别人做迁移数据库时候,遇到一些问题.大致是,如果备份的数据库存在EXTERNAL_ACCESS 和UNSAFE的程序集,那么在还原的时候程序集会出现一些奇怪的错误:

消息 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,那么数据库的拥有者是A\Administrator(如果用windows登录创建),那么但是我们还原到服务器B,那么拥有者可能是B\Administrator,那么SQL CLR的安全性会认为该程序集不可靠.

例如:

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

  1. using System;using System.Data;  
  2. using System.Data.SqlClient;  
  3. using System.Data.SqlTypes;  
  4. using Microsoft.SqlServer.Server;    
  5. public partial class StoredProcedures{   
  6.  [Microsoft.SqlServer.Server.SqlProcedure]      
  7. public static void Test()    {        
  8. // 在此处放置代码    }};编译创建程序集.  
  9.  

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

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

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

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',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 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

【编辑推荐】

  1. SQL Server 2000删除实战演习
  2. SQL Server存储过程的命名标准如何进行?
  3. 卸载SQL Server 2005组件的正确顺序
  4. 对SQL Server字符串数据类型的具体描述
  5. SQL Server数据类型的种类与应用
责任编辑:彭凡 来源: 博客园
相关推荐

2011-03-24 09:24:08

SQL Server数还原

2010-09-14 09:53:52

sql server还

2010-06-17 10:02:12

SQL Server数

2010-07-06 15:07:37

SQL Server

2010-07-08 11:23:41

SQL Server还

2011-03-15 09:55:07

SQL Server2数据库崩溃还原数据库

2010-06-18 12:45:20

SQL Server数

2010-11-10 09:44:31

SQL Server端

2010-07-15 17:28:50

SQL Server

2011-03-10 08:42:15

SQL Server备份还原

2011-04-06 16:15:21

SQL Server数简体繁体

2010-07-08 11:05:14

SQL Server数

2010-07-06 14:40:15

解决SQL Serve

2011-03-24 09:07:11

SQL Server数备份

2011-03-24 09:45:34

SQL Server数恢复

2011-03-15 09:46:20

SQL Server 数据库崩溃

2010-09-27 16:53:49

还原SQL数据库

2021-05-17 06:57:34

SQLServer数据库

2017-05-25 08:52:08

SQL Server数据库

2011-08-03 16:02:25

点赞
收藏

51CTO技术栈公众号