Sunday, 21 February 2010

SQL Server 2005 CLR execution error after restore

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.

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:

USE <DBNAME>GOEXEC 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: