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

SQL Server 2008 and SQL Server 2008 R2 - OUT of SUPPORT July 2019

Both SQL Server 2008 and SQL Server 2008 R2 go out of extended support with Microsoft in July 2019. That’s fast approaching and as we enter ...