Script to sign an unsafe assembly.

SignUnsafeAssembly.sql

SET NOCOUNT ON

DECLARE @cert_name sysname = 'MyLovelyCertificate'
      , @assembly_name nvarchar(4000) = 'MyAssembly'
      , @assembly_path nvarchar(256) = 'c:\MyAssembly.dll'
      , @safe_cert_name sysname
      , @cert_path nvarchar(256)
      , @login_name sysname
      , @sid varbinary(85)
	  , @msg nvarchar(max)
	  , @sql nvarchar(max)
	  , @crlf nvarchar(2) = Char(13) + Char(10)

select @assembly_name = quotename(@assembly_name)
     , @safe_cert_name = quotename(@cert_name)
     , @login_name = quotename(@cert_name + N'Login')

-- *****************************************************************************************
-- * Check that the SID is associated with a valid SQL Server login or Microsoft Windows user, 
-- * otherwise the assembly won't work
-- *****************************************************************************************
SELECT @sid = sid FROM master.sys.database_principals WHERE name = N'dbo'
    
IF SUSER_SNAME(@sid) IS NULL
  BEGIN
    SELECT @msg = 'The database owner SID is not valid on this SQL Server.' + @crlf
                + 'Please use sp_changedbowner @loginame = ''<login ID>'' to change' + @crlf
                + 'the owner of database [' + db_name() + '] to an existing SQL Server login or Microsoft Windows user.'
    RAISERROR (@msg , 16, 1)
    RETURN
  END 
 
IF (@assembly_name is null or @assembly_name = '')
  BEGIN
    RAISERROR('The assembly name needs to be properly defined in the script.', 16, 1)
    RETURN
  END
 
-- *****************************************************************************************
-- Create the assembly
-- *****************************************************************************************
EXEC (N'CREATE ASSEMBLY ' + @assembly_name + N'
AUTHORIZATION [dbo]
FROM ' + @assembly_path + N' -- Replace this with the base64 string of the assembly. `FROM 0x1234A4D...`
WITH PERMISSION_SET = UNSAFE')
 
-- *****************************************************************************************
-- Extract the certificate from the assembly and store it in your database
-- *****************************************************************************************
IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'CREATE CERTIFICATE ' + @safe_cert_name + N' FROM ASSEMBLY ' + @assembly_name + N'')
  END
 
-- *****************************************************************************************
-- Deploy the assembly to the master database to be able to extract the certificate (without
-- resorting to using `BACKUP CERTIFICATE...`). when all is done, drop the assembly again.
-- Note: Trustworthy does not need to be off to be able to extract the cert.
-- *****************************************************************************************
IF NOT EXISTS(SELECT * FROM master.sys.certificates WHERE name = @cert_name) 
  BEGIN   
    SELECT @sql = N'USE master;' + @crlf +  
                  Convert(nvarchar(max), N'CREATE ASSEMBLY ' + @assembly_name + '') + 
                  Convert(nvarchar(max), '
    AUTHORIZATION [dbo]
    FROM ' + @assembly_path + N' -- Replace this with the base64 string of the assembly. `FROM 0x1234A4D...`
	WITH PERMISSION_SET = UNSAFE')
    
    EXEC (@sql)

    EXEC (N'USE master;  
            CREATE CERTIFICATE ' + @safe_cert_name + N' FROM ASSEMBLY ' + @safe_assembly_name + N';')
 
    EXEC(N'USE master;
            DROP ASSEMBLY ' + @safe_assembly_name + N';')

  END

-- *****************************************************************************************
-- Create a login for the certificate. join via the certificate since only 1 login can exist 
-- for a certificate
-- *****************************************************************************************
IF NOT EXISTS(SELECT * 
                FROM sys.server_principals p 
                JOIN master.sys.certificates c 
                  ON p.sid = c.sid
               WHERE c.name = @cert_name)
  BEGIN
    EXEC(N'USE master;
          CREATE LOGIN ' + @login_name + N' FROM CERTIFICATE ' + @safe_cert_name + N';')
  END
 
-- *****************************************************************************************
-- Grant unsafe permission to the login
-- *****************************************************************************************
SELECT @login_name = p.name 
  FROM sys.server_principals p 
  JOIN master.sys.certificates c 
    ON p.sid = c.sid
 WHERE c.name = @cert_name 
 
EXEC (N'USE master;
       GRANT UNSAFE ASSEMBLY TO ' + @login_name + N';')