前端時(shí)間給別人做遷移數(shù)據(jù)庫(kù)時(shí)候,遇到一些問(wèn)題.大致是,如果備份的數(shù)據(jù)庫(kù)存在EXTERNAL_ACCESS 和UNSAFE的程序集,那么在還原的時(shí)候程序集會(huì)出現(xiàn)一些奇怪的錯(cuò)誤:

消息 10314，級(jí)別 16，狀態(tài) 11，第 1 行

在嘗試加載程序集 ID 65536 時(shí) Microsoft .NET Framework 出錯(cuò)。服務(wù)器可能資源不足，或者不信任該程序集，因?yàn)樗?PERMISSION_SET 設(shè)置為 EXTERNAL_ACCESS 或 UNSAFE。請(qǐng)重新運(yùn)行查詢，或檢查有關(guān)的文檔了解如何解決程序集信任問(wèn)題。有關(guān)此錯(cuò)誤的詳細(xì)信息:

System.IO.FileLoadException: 未能加載文件或程序集“testclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null”或它的某一個(gè)依賴項(xiàng)。異常來(lái)自 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)

至于產(chǎn)生的原因大概是,在備份數(shù)據(jù)庫(kù)的時(shí)候,在機(jī)器A,那么數(shù)據(jù)庫(kù)的擁有者是A\Administrator(如果用windows登錄創(chuàng)建),那么但是我們還原到服務(wù)器B,那么擁有者可能是B\Administrator,那么SQL CLR的安全性會(huì)認(rèn)為該程序集不可靠.

例如:

我首先創(chuàng)建一個(gè)簡(jiǎn)單的SQL CLR 存儲(chǔ)過(guò)程:

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]      
public static void Test()    {        
// 在此處放置代碼    }};編譯創(chuàng)建程序集.  
 

在機(jī)器A上的數(shù)據(jù)庫(kù)上執(zhí)行:

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 到機(jī)器B上,然后執(zhí)行:

USE [master];--還原數(shù)據(jù)庫(kù)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;

--如果沒(méi)有啟用CLR,開(kāi)啟EXEC sp_configure 'clr enabled',1RECONFIGURE WITH OVERRIDE; USE test;

--查看程序集,是存在的.SELECT * FROM sys.assemblies;SELECT * FROM sys.assembly_files;

--還原之后的數(shù)據(jù)庫(kù)TRUSTWORTHY 都是OFF的,需要重新設(shè)置ALTER DATABASE test SET TRUSTWORTHY ON; USE test;

--執(zhí)行存儲(chǔ)過(guò)程EXEC dbo.usp_test;

但是一執(zhí)行就報(bào)錯(cuò)了.

解決方案:

在還原數(shù)據(jù)庫(kù)之后,我們可以將數(shù)據(jù)庫(kù)的OWNER設(shè)置成SA.

exec sp_changedbowner 'sa'

再調(diào)用存儲(chǔ)過(guò)程就是成功的.

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

后來(lái)經(jīng)過(guò)一些整理,發(fā)現(xiàn)當(dāng)SQL CLR 存在EXTERNAL_ACCESS或者是UNSAFE的程序集的時(shí)候,SQL Server會(huì)檢查DBO的SID在sys.databases 和sys.server_principals是否一致.

因此我們可能未必一定要修改成sa 的,只要所有者的SID在sys.databases和sys.server_principals 是一致的,就不出問(wèn)題.

我們?cè)赟SMS里面右鍵數(shù)據(jù)庫(kù)屬性->找到文件選項(xiàng)卡->發(fā)現(xiàn)在所有者(是空的,還原以后原來(lái)的SID,數(shù)據(jù)庫(kù)所有者在當(dāng)前的sys.server_principals不匹配的),我們可以在 [...] 里面選擇一個(gè),具有創(chuàng)建CREATE ASSEMLY 權(quán)限的所有者就好,我選擇了B\Administrator,然后測(cè)試 CLR 存儲(chǔ)過(guò)程,沒(méi)問(wèn)題,

引深:

在SQL Server 復(fù)制里面也存在類似的問(wèn)題,就是我們做 "對(duì)等復(fù)制" 的時(shí)候,會(huì)出現(xiàn)DBO不存在,以及sp_replcmd 不存在類似的錯(cuò)誤.其實(shí)也是因?yàn)閷?duì)等復(fù)制初始化訂閱是通過(guò) RESTORE 來(lái)實(shí)現(xiàn)的,因此只要簡(jiǎn)單的修改數(shù)據(jù)庫(kù)所有者 就好了....那么對(duì)等復(fù)制的問(wèn)題也就解決了!!

原文鏈接：http://www.cnblogs.com/bhtfg538/archive/2011/01/18/1938295.html

【編輯推薦】

1. SQL Server 2000刪除實(shí)戰(zhàn)演習(xí)
2. SQL Server存儲(chǔ)過(guò)程的命名標(biāo)準(zhǔn)如何進(jìn)行？
3. 卸載SQL Server 2005組件的正確順序
4. 對(duì)SQL Server字符串?dāng)?shù)據(jù)類型的具體描述
5. SQL Server數(shù)據(jù)類型的種類與應(yīng)用