CREATE ASSEMBLY (Transact-SQL)CREATE ASSEMBLY (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

创建包含类元数据和托管代码的托管应用程序模块,将其作为 SQL ServerSQL Server 实例中的对象。Creates a managed application module that contains class metadata and managed code as an object in an instance of SQL ServerSQL Server. 通过引用此模块,可在数据库中创建公共语言运行时 (CLR) 函数、存储过程、触发器、用户定义聚合以及用户定义类型。By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.

警告

CLR 在 .NET Framework 中使用代码访问安全性 (CAS)(不可再作为安全边界)。CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. 使用 PERMISSION_SET = SAFE 创建的 CLR 程序集可以访问外部系统资源、调用非托管代码以及获取 sysadmin 特权。A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,引入了名为 sp_configureclr strict security 选项,以增强 CLR 程序集的安全性。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. 默认启用 clr strict security,并将 SAFEEXTERNAL_ACCESS 程序集与标记为 UNSAFE 的程序集同等对待。clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. 可禁用 clr strict security 选项以实现后向兼容性,但不建议这样做。The clr strict security option can be disabled for backward compatibility, but this is not recommended. Microsoft 建议所有程序集都通过证书或非对称密钥进行签名,且该证书或非对称密钥具有已在主数据库中获得 UNSAFE ASSEMBLY 权限的相应登录名。Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. 有关详细信息,请参阅 CLR 严格安全性For more information, see CLR strict security.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

CREATE ASSEMBLY assembly_name  
[ AUTHORIZATION owner_name ]  
FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }  
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]  
[ ; ]  
<client_assembly_specifier> :: =  
        '[\\computer_name\]share_name\[path\]manifest_file_name'  
  | '[local_path\]manifest_file_name'  
  
<assembly_bits> :: =  
{ varbinary_literal | varbinary_expression }  

参数Arguments

assembly_name assembly_name
程序集的名称。Is the name of the assembly. 此名称必须在数据库中唯一,并且是有效的标识符The name must be unique within the database and a valid identifier.

AUTHORIZATION owner_name AUTHORIZATION owner_name
指定作为程序集所有者的用户或角色的名称。Specifies the name of a user or role as owner of the assembly. owner_name 必须是当前用户所属的角色的名称,或当前用户必须具有对 owner_name 的 IMPERSONATE 权限 。owner_name must either be the name of a role of which the current user is a member, or the current user must have IMPERSONATE permission on owner_name. 如果未指定,则所有权授予当前用户。If not specified, ownership is given to the current user.

<client_assembly_specifier><client_assembly_specifier>
指定正在上载的程序集所在的本地路径或网络位置,以及与程序集对应的清单文件名。Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest file name that corresponds to the assembly. <client_assembly_specifier> 可表示为固定字符串或其值等于固定字符串的、带有变量的表达式。<client_assembly_specifier> can be expressed as a fixed string or an expression evaluating to a fixed string, with variables. CREATE ASSEMBLY 不支持加载多模块程序集。CREATE ASSEMBLY does not support loading multimodule assemblies. SQL ServerSQL Server 还将在同一位置查找此程序集的所有相关程序集,并使用与根级别程序集相同的所有者将其上载。also looks for any dependent assemblies of this assembly in the same location and also uploads them with the same owner as the root level assembly. 如果没有找到这些相关程序集且它们尚未加载到当前数据库中,则 CREATE ASSEMBLY 失败。If these dependent assemblies are not found and they are not already loaded in the current database, CREATE ASSEMBLY fails. 如果相关程序集已加载到当前数据库中,则这些程序集的所有者必须与新创建的程序集的所有者相同。If the dependent assemblies are already loaded in the current database, the owner of those assemblies must be the same as the owner of the newly created assembly.

重要

Azure SQL 数据库不支持通过文件创建程序集。Azure SQL Database does not support creating an assembly from a file.

如果模拟登录用户,则无法指定 <client_assembly_specifier>。<client_assembly_specifier> cannot be specified if the logged in user is being impersonated.

<assembly_bits><assembly_bits>
组成程序集和依赖程序集的二进制值的列表。Is the list of binary values that make up the assembly and its dependent assemblies. 列表中的第一个值将视为根级程序集。The first value in the list is considered the root-level assembly. 与相关程序集对应的值可以按任意顺序提供。The values corresponding to the dependent assemblies can be supplied in any order. 所有与根程序集的依赖项不相对应的值都将忽略。Any values that do not correspond to dependencies of the root assembly are ignored.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

varbinary_literal varbinary_literal
是 varbinary 文本。Is a varbinary literal.

varbinary_expressionvarbinary_expression
varbinary 类型的表达式。Is an expression of type varbinary.

PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }

重要

PERMISSION_SET 选项受 clr strict security 选项的影响,首次警告中给予了相关说明。The PERMISSION_SET option is affected by the clr strict security option, described in the opening warning. 如果启用 clr strict security,所有程序集被视为 UNSAFEWhen clr strict security is enabled, all assemblies are treated as UNSAFE.

指定 SQL ServerSQL Server 访问程序集时向程序集授予的一组代码访问权限。Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL ServerSQL Server. 如果未指定,则将 SAFE 用作默认值。If not specified, SAFE is applied as the default.

我们推荐使用 SAFE。We recommend using SAFE. SAFE 是最具限制性的权限集。SAFE is the most restrictive permission set. 由具有 SAFE 权限的程序集所执行的代码将无法访问外部系统资源,例如文件、网络、环境变量或注册表。Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.

EXTERNAL_ACCESS 使程序集可以访问某些外部系统资源,例如文件、网络、环境变量以及注册表。EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

UNSAFE 可使程序集不受限制地访问资源,无论是 SQL ServerSQL Server 实例内部还是外部的资源都可以访问。UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL ServerSQL Server. 从 UNSAFE 程序集内运行的代码可调用未托管代码。Code running from within an UNSAFE assembly can call unmanaged code.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

重要

对于执行计算和数据管理任务而无需访问 SQL ServerSQL Server 实例外部资源的程序集,SAFE 是推荐的权限设置。SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside an instance of SQL ServerSQL Server.

对于访问 SQL ServerSQL Server 实例外部资源的程序集,我们推荐使用 EXTERNAL_ACCESS。We recommend using EXTERNAL_ACCESS for assemblies that access resources outside of an instance of SQL ServerSQL Server. EXTERNAL_ACCESS 程序集包含 SAFE 程序集的可靠性和可伸缩性保护,但从安全角度而言,它与 UNSAFE 程序集类似。EXTERNAL_ACCESS assemblies include the reliability and scalability protections of SAFE assemblies, but from a security perspective are similar to UNSAFE assemblies. 原因是在默认情况下,EXTERNAL_ACCESS 程序集中的代码以 SQL ServerSQL Server 服务帐户身份运行并访问此帐户的外部资源,除非此代码显式模拟调用方。This is because code in EXTERNAL_ACCESS assemblies runs by default under the SQL ServerSQL Server service account and accesses external resources under that account, unless the code explicitly impersonates the caller. 因此,创建 EXTERNAL_ACCESS 程序集的权限应只授予以 SQL ServerSQL Server 服务帐户身份运行代码的可信登录。Therefore, permission to create EXTERNAL_ACCESS assemblies should be granted only to logins that are trusted to run code under the SQL ServerSQL Server service account. 有关模拟的详细信息,请参阅 CLR 集成安全性For more information about impersonation, see CLR Integration Security.

指定 UNSAFE 将使程序集中的代码在 SQL ServerSQL Server 进程空间中完全自由地执行操作,但这些操作可能危及 SQL ServerSQL Server 的可靠性。Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL ServerSQL Server process space that can potentially compromise the robustness of SQL ServerSQL Server. UNSAFE 程序集也可能破坏 SQL ServerSQL Server 或公共语言运行时的安全系统。UNSAFE assemblies can also potentially subvert the security system of either SQL ServerSQL Server or the common language runtime. UNSAFE 权限只应授予高度可信的程序集。UNSAFE permissions should be granted only to highly trusted assemblies. 只有 sysadmin 固定服务器角色的成员才能创建并更改 UNSAFE 程序集。Only members of the sysadmin fixed server role can create and alter UNSAFE assemblies.

有关程序集的权限集的详细信息,请参阅设计程序集For more information about assembly permission sets, see Designing Assemblies.

备注Remarks

CREATE ASSEMBLY 将上载以前由托管代码编写为 .dll 文件的程序集,以便在 SQL Server 实例中使用。CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

启用时,PERMISSION_SETCREATE ASSEMBLY 语句中的 ALTER ASSEMBLY 选项在运行时将被忽略,但元数据中将保留 PERMISSION_SET 选项。When enabled, the PERMISSION_SET option in the CREATE ASSEMBLY and ALTER ASSEMBLY statements is ignored at run-time, but the PERMISSION_SET options are preserved in metadata. 忽略选项可最大程度减少中断现有代码语句。Ignoring the option, minimizes breaking existing code statements.

SQL ServerSQL Server 不允许使用相同的名称、区域性和公钥来注册程序集的不同版本。does not allow registering different versions of an assembly with the same name, culture and public key.

当尝试访问 <client_assembly_specifier> 中指定的程序集时,SQL ServerSQL Server 模拟当前 Windows 登录的安全上下文。When attempting to access the assembly specified in <client_assembly_specifier>, SQL ServerSQL Server impersonates the security context of the current Windows login. 如果 <client_assembly_specifier> 指定了网络位置(UNC 路径),则由于委托限制,当前登录名的模拟将不应用于网络位置。If <client_assembly_specifier> specifies a network location (UNC path), the impersonation of the current login is not carried forward to the network location because of delegation limitations. 在这种情况下,将使用 SQL ServerSQL Server 服务帐户的安全上下文进行访问。In this case, access is made using the security context of the SQL ServerSQL Server service account. 有关详细信息,请参阅凭据(数据库引擎)For more information, see Credentials (Database Engine).

除了 assembly_name 指定的根程序集外,SQL ServerSQL Server 还试图上传正在上传的根程序集所引用的所有程序集。Besides the root assembly specified by assembly_name, SQL ServerSQL Server tries to upload any assemblies that are referenced by the root assembly being uploaded. 如果前面的 CREATE ASSEMBLY 语句已将被引用的程序集上载到数据库中,则不再上载此程序集,但它仍可用于根程序集。If a referenced assembly is already uploaded to the database because of an earlier CREATE ASSEMBLY statement, this assembly is not uploaded but is available to the root assembly. 如果以前未上载相关程序集,但 SQL ServerSQL Server 无法在源目录中找到它的清单文件,则 CREATE ASSEMBLY 将返回错误。If a dependent assembly was not previously uploaded, but SQL ServerSQL Server cannot locate its manifest file in the source directory, CREATE ASSEMBLY returns an error.

如果根程序集引用的所有相关程序集尚未在数据库中并且与根程序集一起隐式加载,则它们与根级别程序集具有相同的权限设置。If any dependent assemblies referenced by the root assembly are not already in the database and are implicitly loaded together with the root assembly, they have the same permission set as the root level assembly. 如果必须使用不同于根级别程序集的权限设置创建相关程序集,则它们必须在具有相应权限设置的根级别程序集之前显式上载。If the dependent assemblies must be created by using a different permission set than the root-level assembly, they must be uploaded explicitly before the root level assembly with the appropriate permission set.

程序集验证Assembly Validation

SQL ServerSQL Server 将对 CREATE ASSEMBLY 语句上载的程序集二进制文件执行检查,以确保符合以下要求:performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:

  • 程序集二进制文件具有格式正确的有效元数据和代码段,并且代码段包含有效的 Microsoft 中间语言 (MSIL) 指令。The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.

  • 所引用的一组系统程序集是 SQL ServerSQL Server 中以下支持的程序集之一:Microsoft.Visualbasic.dll、Mscorlib.dll、System.Data.dll、System.dll、System.Xml.dll、Microsoft.Visualc.dll、Custommarshallers.dll、System.Security.dll、System.Web.Services.dll、System.Data.SqlXml.dll、System.Core.dll 和 System.Xml.Linq.dll。The set of system assemblies it references is one of the following supported assemblies in SQL ServerSQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, System.Data.SqlXml.dll, System.Core.dll, and System.Xml.Linq.dll. 还可引用其他系统程序集,但这些程序集必须在数据库中显式注册。Other system assemblies can be referenced, but they must be explicitly registered in the database.

  • 对于使用 SAFE 或 EXTERNAL ACCESS 权限集创建的程序集:For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:

    • 程序集代码应是类型安全的。The assembly code should be type-safe. 通过对程序集运行公共语言运行时验证工具可建立类型安全。Type safety is established by running the common language runtime verifier against the assembly.

    • 程序集的类中不应包含任何静态数据成员,除非这些成员标记为只读。The assembly should not contain any static data members in its classes unless they are marked as read-only.

    • 程序集中的类不能包含终结器方法。The classes in the assembly cannot contain finalizer methods.

    • 程序集的类或方法只能使用允许的代码属性进行注释。The classes or methods of the assembly should be annotated only with allowed code attributes. 有关详细信息,请参阅 CLR 例程的自定义属性For more information, see Custom Attributes for CLR Routines.

除了执行 CREATE ASSEMBL 时进行的上述检查外,在执行程序集中代码时还应进行其他检查:Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:

  • 如果程序集的权限集不包括此权限,则调用某些需要特定代码访问权限的 MicrosoftMicrosoft.NET Framework.NET Framework API 可能失败。Calling certain MicrosoftMicrosoft.NET Framework.NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.

  • 对于 SAFE 和 EXTERNAL_ACCESS 程序集,对使用某些 HostProtectionAttribute 注释的 .NET Framework.NET Framework API 的任何调用尝试都将失败。For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework.NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.

有关详细信息,请参阅设计程序集For more information, see Designing Assemblies.

权限Permissions

需要 CREATE ASSEMBLY 权限。Requires CREATE ASSEMBLY permission.

如果指定 PERMISSION_SET = EXTERNAL_ACCESS,则需要对服务器的 EXTERNAL ACCESS ASSEMBLY 权限。If PERMISSION_SET = EXTERNAL_ACCESS is specified, requiresEXTERNAL ACCESS ASSEMBLY permission on the server. 如果指定 PERMISSION_SET = UNSAFE,则需要对服务器的 UNSAFE ASSEMBLY 权限。If PERMISSION_SET = UNSAFE is specified, requires UNSAFE ASSEMBLY permission on the server.

如果程序集已经存在于数据库中,则用户必须是将上载的程序集所引用的所有程序集的所有者。User must be the owner of any assemblies that are referenced by the assembly that are to be uploaded if the assemblies already exist in the database. 若要使用文件路径上传程序集,则当前用户必须是经过 Windows 身份验证的登录名或 sysadmin 固定服务器角色的成员。To upload an assembly by using a file path, the current user must be a Windows authenticated login or a member of the sysadmin fixed server role. 执行 CREATE ASSEMBLY 的用户的 Windows 登录名必须对此语句中加载的共享和文件具有读取权限。The Windows login of the user that executes CREATE ASSEMBLY must have read permission on the share and the files being loaded in the statement.

CLR 严格安全性的权限Permissions with CLR strict security

启用 CLR strict security 时,创建 CLR 程序集需要以下权限:The following permissions required to create a CLR assembly when CLR strict security is enabled:

  • 用户必须具有 CREATE ASSEMBLY 权限The user must have the CREATE ASSEMBLY permission
  • 并且,还必须满足以下条件之一:And one of the following conditions must also be true:
    • 使用具有相应登录名(该登录名对应于服务器上的 UNSAFE ASSEMBLY 权限)的证书或非对称密钥对程序集进行签名。The assembly is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server. 建议对程序集进行签名。Signing the assembly is recommended.
    • 数据库的 TRUSTWORTHY 属性设置为 ON,且数据库由在服务器上具有 UNSAFE ASSEMBLY 权限的登录名所有。The database has the TRUSTWORTHY property set to ON, and the database is owned by a login that has the UNSAFE ASSEMBLY permission on the server. 不建议使用此选项。This option is not recommended.

有关程序集的权限集的详细信息,请参阅设计程序集For more information about assembly permission sets, see Designing Assemblies.

示例Examples

示例 A:从 dll 创建程序集Example A: Creating an assembly from a dll

适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

以下示例假定:SQL Server 数据库引擎SQL Server Database Engine示例安装在本地计算机的默认位置,且 HelloWorld.csproj 示例应用程序已编写。The following example assumes that the SQL Server 数据库引擎SQL Server Database Engine samples are installed in the default location of the local computer and the HelloWorld.csproj sample application is compiled. 有关详细信息,请参阅 Hello World 示例For more information, see Hello World Sample.

CREATE ASSEMBLY HelloWorld   
FROM <system_drive>:\Program Files\Microsoft SQL Server\100\Samples\HelloWorld\CS\HelloWorld\bin\debug\HelloWorld.dll  
WITH PERMISSION_SET = SAFE;  

重要

Azure SQL 数据库不支持通过文件创建程序集。Azure SQL Database does not support creating an assembly from a file.

示例 B:从程序集位创建程序集Example B: Creating an assembly from assembly bits

适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

将示例位(不完整或无效)替换为程序集位。Replace the sample bits (which are not complete or valid) with your assembly bits.

CREATE ASSEMBLY HelloWorld  
    FROM 0x4D5A900000000000  
WITH PERMISSION_SET = SAFE;  

另请参阅See Also

ALTER ASSEMBLY (Transact-SQL) ALTER ASSEMBLY (Transact-SQL)
DROP ASSEMBLY (Transact-SQL) DROP ASSEMBLY (Transact-SQL)
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
CREATE TYPE (Transact-SQL) CREATE TYPE (Transact-SQL)
CREATE AGGREGATE (Transact-SQL) CREATE AGGREGATE (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
公共语言运行时 (CLR) 集成的使用方案和示例Usage Scenarios and Examples for Common Language Runtime (CLR) Integration