title: SQL Server CLR Assembly Repeatable Implementation Ordering?
body: <p>I have a pair of CLR assemblies and I'm trying to create a repeatable implementation script.
  Both assemblies need the EXTERNAL permission set, so I've got asymmetric keys involved as well, and these are causing problems.  As when you run it the second time you get the 'Cannot drop key, login mapped to it' error.</p>
  
  <p>Some other q's I have with this are:
  Do I need the login/key in the database and at server level?</p>
  
  <p>At the moment my script is:</p>
  
  <pre><code>USE master
  GO 
  
  IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'BaselCLRLogin')
      DROP LOGIN [BaselCLRLogin]
  GO
  
  IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'BaselCLRKey')
      DROP ASYMMETRIC KEY [BaselCLRKey]
  GO
  
  USE [BaselST]
  GO
  
  IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('clr.CheckFileFormat'))
      DROP PROCEDURE clr.CheckFileFormat
  GO
  
  IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BaselCLRLogin')
      DROP USER [BaselCLRLogin]
  GO
  
  IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'BaselCLR' and is_user_defined = 1)
      DROP ASSEMBLY [BaselCLR]
  GO
  
  IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'BaselCLRKey')
      DROP ASYMMETRIC KEY [BaselCLRKey]
  GO
  
  USE master
  GO 
  
  -- First Create the Asymmetric Key from the Assembly
  CREATE ASYMMETRIC KEY BaselCLRKey
  FROM EXECUTABLE FILE = 'D:\BASEL\BS_Assemblies\BaselCLR.dll'
  GO
  
  -- Create the Login from the Asymmetric Key
  CREATE LOGIN BaselCLRLogin FROM ASYMMETRIC KEY BaselCLRKey
  GO
  
  -- Grant the External Access Priviledge to the Login
  GRANT EXTERNAL ACCESS ASSEMBLY TO BaselCLRLogin
  GO
  
  USE [BaselST] 
  GO
  
  IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'clr')
      EXEC ('CREATE SCHEMA [clr]')
  GO
  
  -- Add a database user in the SQLCLR_Net Database for the Login
  CREATE USER [BaselCLRLogin] FOR LOGIN [BaselCLRLogin]
  GO
  
  CREATE ASSEMBLY BaselCLR
  FROM 'D:\BASEL\BS_Assemblies\BaselCLR.dll'
  WITH PERMISSION_SET = EXTERNAL_ACCESS
  GO
  
  CREATE PROCEDURE clr.CheckFileFormat(
      @fileName nvarchar(255)
      , @seperator nvarchar(1)
      , @lineTerminator tinyint
      , @encoding tinyint
      , @headerFieldCount int
      , @bodyFieldCount int
      , @footerFieldCount int
      , @reason nvarchar(max) out)
  AS
  EXTERNAL NAME BaselCLR.StoredProcedures.CheckFileFormat
  GO
  </code></pre>
  
  <p>If there's anything else I've done that are no, no's let me know!</p>