Problems with SQL Server Assembly (SQL CLR) after restoring a database

An issue that was encountered following a restore of the database (SQL Server 2008) when the project was recently migrated:

msg 10314, Level 16, State 11, line 1th
the Microsoft. NET Framework error occurred while attempting to load assembly ID 65536. The server may be out of resources or do not trust the assembly because its permission_set is set to external_access or unsafe. Rerun the query, or check the documentation about how to troubleshoot assembly trust issues. For more information about this error:
System.IO.FileLoadException: Failed to load 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, Evidence assemblysecurity, Assembly Lo Cationhint, 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)

Reason

Server AThis issue occurs because the logon name that you use on the CREATE database is not Server B on the instance of SQL Server 2005. This login may be a Microsoft Windows logon or a SQL Server login.

use [master];

--Restore Database

With RECOVERY,

' E:\data\test.mdf ',

' E:\data\test.ldf ', REPLACE;

--If the CLR is not enabled, turn on

' CLR enabled ', 1

With OVERRIDE;

Use test;

--View the assembly, is present.

From Sys.assemblies;

From Sys.assembly_files;

--The database trustworthy after the restore is off and needs to be reset

On;

Use test;

--Execute Stored procedure

EXEC dbo.usp_test;

but the implementation of the error.

Workaround:

After restoring the database, we can set the owner of the database to SA.

First step: Use <databasename>

exec sp_changedbowner ' sa '

Step two: Alter DATABASE IDMTTD set trustworthy on;

Step Three:

SELECT * FROM sys.databases

SELECT * FROM Sys.server_principals

Verify that the SID in the table that corresponds to OWNER_SID in the databases table exists. The main point is to see if the database owner exists and the owner may not exist while replicating the database.

The re-invocation of the stored procedure is successful.

can view: KB http://support.microsoft.com/kb/918040

Later, after some sorting, it was found that SQL Server checks the dbo SID in Sys.databases and Sys.server_ when the SQL CLR has external_access or unsafe assemblies Whether the principals is consistent.

So we may not necessarily have to change to SA, as long as the owner's SID in Sys.databases and sys.server_principals is consistent, there is no problem.

We're in SSMs. Right-click Database Properties-Find File tab-found in the owner (is empty, after restoring the original SID, the database owner does not match in the current sys.server_principals), we can select one in [...] With the owner who created the Create Assemly right, I chose B\administrator, and then I tested the CLR stored procedure, no problem,

Lead Depth:

There is a similar problem with SQL Server replication, when we do a "peer-copy", the DBO does not exist, and Sp_replcmd does not have a similar error. It is also because peer-to-copy initialization subscriptions are implemented through RESTORE, So just simply modify the database owner is good .... Then the issue of peer replication will be solved!!

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

Problems with SQL Server Assembly (SQL CLR) after restoring a database