• Article

CREATE ASSEMBLY (Transact-SQL)

Creates a managed application module that contains class metadata and managed code as an object in an instance of SQL Server. 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.

Topic link iconTransact-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
    Is the name of the assembly. The name must be unique within the database and a valid identifier.

  • AUTHORIZATION owner_name
    Specifies the name of a user or role as owner of the assembly. owner_namemust 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>
    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> can be expressed as a fixed string or an expression evaluating to a fixed string, with variables. CREATE ASSEMBLY does not support loading multimodule assemblies. SQL 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. 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.

    <client_assembly_specifier> cannot be specified if the logged in user is being impersonated.

  • <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.

  • varbinary_literal
    Is a varbinary literal.

  • varbinary_expression
    Is an expression of type varbinary.

  • PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }
    Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL Server. If not specified, SAFE is applied as the default.

    We recommend using SAFE. SAFE is the most restrictive permission set. 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 enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.

    UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code.

    Security noteSecurity Note

    SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside an instance of SQL Server.

    We recommend using EXTERNAL_ACCESS for assemblies that access resources outside of an instance of SQL Server. EXTERNAL_ACCESS assemblies include the reliability and scalability protections of SAFE assemblies, but from a security perspective are similar to UNSAFE assemblies. This is because code in EXTERNAL_ACCESS assemblies runs by default under the SQL Server service account and accesses external resources under that account, unless the code explicitly impersonates the caller. Therefore, permission to create EXTERNAL_ACCESS assemblies should be granted only to logins that are trusted to run code under the SQL Server service account. For more information about impersonation, see CLR Integration Security.

    Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies. 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 uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

SQL Server does not allow registering different versions of an assembly with the same name, culture and public key.

When attempting to access the assembly specified in <client_assembly_specifier>, SQL Server impersonates the security context of the current Windows login. 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. In this case, access is made using the security context of the SQL Server service account. For more information, see Credentials (Database Engine).

Besides the root assembly specified by assembly_name, SQL Server tries to upload any assemblies that are referenced by the root assembly being uploaded. 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. If a dependent assembly was not previously uploaded, but SQL 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 Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:

  • The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.

  • The set of system assemblies it references is one of the following supported assemblies in SQL 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.

  • 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. For more information, see Custom Attributes for CLR Routines.

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:

  • Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.

  • For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.

For more information, see Designing Assemblies.

Permissions

Requires CREATE ASSEMBLY permission.

If PERMISSION_SET = EXTERNAL_ACCESS is specified, the SQL Server login must have EXTERNAL ACCESS ASSEMBLY permission on the server. If PERMISSION_SET = UNSAFE is specified, membership in the sysadmin fixed server role is required.

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. 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. 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.

For more information about assembly permission sets, see Designing Assemblies.

Examples

The following example assumes that the SQL Server Database Engine samples are installed in the default location of the local computer and the HelloWorld.csproj sample application is compiled. For more information, see Hello World Sample. 

DECLARE @SamplesPath nvarchar(1024)
SELECT @SamplesPath = REPLACE(physical_name, 
    'Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf', 
    'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
FROM master.sys.database_files 
WHERE name = 'master';
CREATE ASSEMBLY HelloWorld 
FROM @SamplesPath + 'HelloWorld\CS\HelloWorld\bin\debug\HelloWorld.dll'
WITH PERMISSION_SET = SAFE;