I recently had an Sql Server 2014 r2 server I needed to migrate to Sql Server 2019 r2 with a set of CLR assemblies on it. My plan is to detach each database and reattach them on the new box. The assemblies are transferred along with the database, but they won’t run. I went through the following steps to run them.

Step 1. Click on Programmability, Assemblies under the Database in Sql Server Management Studio.

Step 2: Right click and choose Script Assembly as Create to New Query Editor Window

you’ll get something that looks like this

CREATE ASSEMBLY [Microsoft.SqlServer.Types]

from   XXXXXX

with permission_set=unsafe

Step #3, run the query select * from sys.assemblies you’ll get something that looks like this

Step #4 You have to make the assemblies trusted. Run the following code. Substitute the XXXXX where indicated and the clr_name from above

–run from create assembly after from copied to @asmbin
USE master;
GO
DECLARE @clrName nvarchar(4000) = ‘microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil’
DECLARE @asmBin varbinary(max) = XXXXXXXX
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES(‘SHA2_512’, @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;

go

Step #5. Here’s the query to list the trusted assemblies (I put XXXX for hash,  you can take that out)

SELECT ‘XXXX’ as hash, description, create_date
FROM sys.trusted_assemblies

run as

SELECT hash, description, create_date
FROM sys.trusted_assemblies

with these results

I recently migrated a SQL Server 2008 R2 database to SQL Server 2014.  We host with Rackspace, so I had them restore our databases from backup to the new server. I was left with database users that weren’t linked to SQL Server Users.  Even after I added the users they were still not linked together and the system wouldn’t offer me the option. The following SQL allowed me to fix the problem.

1. Assign ownership of the database to a current user on the new server Alter authorization on database::[DATABASENAME] to [DOMAIN\USERNAME]
2. Find the users that aren’t associated with their SQL Login with Exec sp_change_users_login ‘Report’
3. Autofix the logins to link the SQL Server Database User to the SQL Server Login User exec sp_change_users_Login ‘Auto_Fix’,’USERNAME’

The new database clearly won’t have the SQL Server Login Users until you add them so add them manually before you run the above routines.