4533.aspx

Assembly load error in SQL Server 2005 after attaching DB

I recently migrated a Microsoft SQL Server database from one disk drive to another using the sp_detach_db and sp_attach_db stored procedures.

It was on the same machine so I was surprised when I got this half English, half Italian error message:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65733. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:   System.IO.FileLoadException: Impossibile caricare il file o l'assembly '..., Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' o una delle relative dipendenze. Errore relativo alla protezione. (Eccezione da HRESULT: 0x8013150A)  System.IO.FileLoadException:      at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)     at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)     at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)     at System.Reflection.Assembly.Load(String assemblyString)    The statement has been terminated.

Turns out that the DB had a couple of CLR stored procedures and that they refused to work after the move. SQL Server could find the assemblies but it was still unable to load them. The trust configuration was OK as well.

The problem was that the commands were executed from the SQL Server Development Studio using an account connected via windows integrated security. As a result the DB was attached using the current user, not "sa", as the owner of the DB.

Using sp_changedbowner to set sa as the owner fixed the problem.