SQL Server CLR and Authorization

Over the past year I have worked on a few projects that have used SQL CLR procedures and functions.  SQL Server needs to be configured to run these CLRs.  A standard script to get this done would be:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);
GO

This will generally get your CLR up and running on your server if it's the first time you have installed the CLR. What I have noticed though is that if you backup a database and restore it on another SQL Server in a different Domain with different security setting the CLR stops working with an error like this:

"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. 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: "

To get your CLRs working again you need to correct the Authorization on the database with the CLR.  Here is a script that will allow you to do this:

EXEC sp_configure 'clr enabled', 1;
-- To get the owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

-- To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

-- The sid's above should be the same
-- To fix:
/*
ALTER AUTHORIZATION ON Database::XXXXX TO [domain\user]
*/

 

Note the SELECT statements are merely going to show that the SID for the owner of the current database differs to what the master database has recorded as the SID for the current database.  To fix the SID on the current database run the ALTER AUTHORIZATION statement replacing the XXXXX with the current database name and the [domain\user] with the user you want to give authorization to e.g. [sa].  Once the authorization has been altered run the first script above to ensure CLR is enabled and TRUSTWORTHY is ON.