After SQL Server Assembly restores the database

When the front-end time is used to migrate databases to others, some problems are encountered. Generally, if the backup database has EXTERNAL_ACCESS and UNSAFE assemblies, some strange errors may occur during the restoration process:

Message 10314, level 16, status 11, 1st rows

Microsoft. NET Framework error when trying to load Assembly ID 65536. The server may have insufficient resources or do not trust the Assembly because its PERMISSION_SET is set to EXTERNAL_ACCESS or UNSAFE. Run the query again or check related documents to learn how to solve the Assembly Trust problem. For details about this error:

System. IO. FileLoadException: failed to load the file or assembly "testclr, Version = 0.0.0.0, Culture = neutral, PublicKeyToken = null" or one of its dependencies. Exception from HRESULT: 0x80FC80F1
System. IO. FileLoadException:

In System. Reflection. Assembly. _ nLoad (AssemblyName fileName, String codeBase, evi1_assemblysecurity, Assembly locationHint, StackCrawlMark & stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

In System. Reflection. Assembly. InternalLoad (AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark & stackMark, Boolean forIntrospection)

In System. Reflection. Assembly. InternalLoad (String assemblyString, Evidence assemblySecurity, StackCrawlMark & stackMark, Boolean forIntrospection)

In System. Reflection. Assembly. Load (String assemblyString)

The cause is that when you back up the database, the database owner is A \ Administrator (if you log on to windows to create A database ), but if we restore to server B, the owner may be B \ Administrator, so the SQL CLR security will regard the Assembly as unreliable.

For example:

First, create a simple SQL CLR Stored Procedure:

 
 

  
  
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 (){
  
  
// Place the code here}; compile and create the assembly.
  
  
 
 
 

Run the following command on the database on machine:

 
 

  
  
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;  
  
  
 
 
 

Copy c: \ test. bak to machine B, and then execute:

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;

-- If CLR is not enabled, enable EXEC sp_configure 'clr enabled', 1 reconfigure with override; USE test;

-- Check the Assembly. SELECT * FROM sys. assemblies; SELECT * FROM sys. assembly_files;

-- TRUSTWORTHY in the restored DATABASE is OFF. You need to reset alter database test set trustworthy on; USE test;

-- Execute the Stored Procedure EXEC dbo. usp_test;

But an error is reported as soon as it is executed.

Solution:

After restoring the database, we can set the database OWNER to SA.

Exec sp_changedbowner 'sa'

Calling the stored procedure is successful.

View: KB http://support.microsoft.com/kb/918040

After some sorting, it is found that when SQL clr has EXTERNAL_ACCESS or UNSAFE assembly, SQL Server will check whether DBO SID is consistent in sys. databases and sys. server_principals.

Therefore, we may not have to change it to sa. As long as the owner's SID is consistent with sys. databases and sys. server_principals, no problem will occur.

In SSMS, right-click Database properties and choose files from the shortcut menu. On the files tab, you can find that the original SID is stored in the current sys. server_principals does not match), we can go to [...] select an owner with the create assemly permission. I chose B \ Administrator and tested the CLR Stored Procedure. No problem,

Deep Dive:

Similar problems exist in SQL Server replication, that is, when we perform "peer replication", DBO does not exist, and sp_replcmd does not have similar errors. in fact, it is also because the initial subscription of peer replication is implemented through RESTORE, so you only need to modify the database owner simply .... the peer replication problem will be solved !!

Edit recommendations]