I recently restored a SQL Server 2005 database to a testing environment. The database contained several CLR assemblies, when the tester tried to execute one of the CLR’s SQL returned the following error:
An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.
After a little search on Google, i came across the following Knowledge Base article http://support.microsoft.com/default.aspx/kb/918040
A quick check in SSMS showed that I the database had been restored with an owner other than SA. So I run the following script to change the database owner:
EXEC sp_changedbowner 'sa'
I then get errors relating to trustworthy setting on the database, after checking in SSMS again I saw that the database trustworthy setting was set to false. I used the following Script to set the trustworthy setting:
ALTER DATABASE <DBNAME>SET TRUSTWORTHY ON
This fixed the issue.