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.

After a little search on Google, i came across the following Knowledge Base article 

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:

USE master

This fixed the issue.

No comments:

Post a Comment

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...