SQL Server stops loading assembly

Solution 1:

Assemblies with EXTERNAL_ACCESS are, through some convoluted path, falling under the EXECUTE AS path. The problem appears when the 'dbo' cannot be mapped to a valid login. dbo's login is the login with the SID the owner_sid value in sys.databases. Unless an AUTHORIZATION clause was used in CREATE DATABASE the owner_sid is the login sid of the principal issuing the CREATE DATABASE statement. Most times this is the Windows SID of the user logged in and issuing the CREATE DATABASE. With this knowledge in hand one can easily envision the problems that may arise:

• copy database: CREATE DATABASE was issued on machine A by an user local to A (ie. MachineA\user or DomainA\user) then the database was copied to machine B (via backup/restore or via file copy). The owner_sid is preserved by file copy as well as by backup/restore, this on machine B the owner_sid is invalid. Everything requiring EXECUTE As fails, including loading assemblies from the database.
• tombstoned account. CREATE DATABASE was issued by an user that has left the company. The AD account is deleted and all of the sudden EXECUTE AS mysteriously fails, including loading assemblies.
• disconnected laptop. CREATE DATABASE was issues when the laptop was connected in the work network. At home you can log in using Windows cached credentials, but EXECUTE AS wants to connect to the unavailable AD and fails. Loading assemblies also fails. Problems mysteriously resolves itself next day at work, when you're again within reach of AD.
• spotty AD connectivity. The EXECUTE AS does not uses system cached credentials and connects to the AD every time. If the AD connectivity has issues (timeout, errors) those issues manifest as similar timeouts and errors in EXECUTE AS, including loading assemblies

All these issues can be diagnosed by simply running: EXECUTE AS USER = 'dbo'; in the context of the problem db. It it fails with an error then the cause of your assembly load problems is the EXECUTE AS context of dbo.

The solution is trivial, simply force the owner_sid to a valid login. sa is the usually the best candidate:

ALTER AUTHORIZATION ON DATABASE::[<dbanme>] TO sa;

The funny thing is that the database may seem to be perfectly healthy; tables are available and you can run selects, updates, deletes, create and drop tables etc. Only certain components require EXECUTE AS:

• code signing requires the code to have an EXECUTE AS clause
• assembly validation
• explicit EXECUTE AS in T-SQL code
• Service Broker message delivery (including Query Notifications)

The latter is the most often seen culprit, as applications relying on SqlDependency all of a sudden seem to stop working, or have random problems. This article explains how SqlDependency ultimately depends on EXECUTE AS: The Mysterious Notification

Solution 2:

I experienced it. it seems when you restore a database TRUSTWORTHY set to OFF. so my solution was to turn it on :

ALTER DATABASE [myDB] SET TRUSTWORTHY ON
GO

and after i turned it on, my triggers and stored procedures started to work like before.

Solution 3:

Just in case someone comes across this problem, the solution that worked for me was:

ALTER AUTHORIZATION ON DATABASE::[mydb] TO sa;

followed by

ALTER DATABASE [mydb]  SET TRUSTWORTHY ON;

I am restoring my db with the Administrator account, and nothing else other than the combination of these two calls has worked for me.

Substitute [mydb] for [yourdatabasename]