SQL Server CLR程序集可重复实现顺序？

Question

我有一对CLR程序集，并且我正在尝试创建一个可重复的实现脚本。这两个程序集都需要外部权限集，所以我还涉及到了非对称密钥，这些都会导致问题。因为当你第二次运行它时，你得到了'Cannot drop key，login mapped to it‘错误。

我对此提出的其他一些问题是:我是否需要在数据库和服务器级别上的登录/密钥？

目前我的脚本是：

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

如果我还做了什么不，不，请让我知道！

Answer 1

问题出在下面这行……

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = 'BaselCLRLogin')

您在错误的系统表中查找登录名！

改为从sys.syslogins中选择。

IF EXISTS (SELECT * FROM sys.syslogins WHERE [sid] = SUSER_SID('BaselCLRLogin'))
    DROP LOGIN [BaselCLRLogin]
GO

Answer 2

必须使用不同的密钥对每个程序集进行签名。