Create Assembly

CREATE ASSEMBLY

Upload an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

An Assemby object is a managed application module, other objects may reference this module - stored procedures, triggers, CLR functions, user-defined aggregates/types.

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 }

Key:
   assembly_name  The (unique) name of the assembly.
   owner_name     The usernname or role as owner of the assembly.
   client_assembly_specifier  The local path or network location for the assembly being uploaded.
                  A fixed string or an expression evaluating to a fixed string. (Assembly manifest filename)
   assembly_bits  List of binary values that make up the assembly and it's 
                  dependent assemblies, starting with the root-level assembly.
   PERMISSION_SET SAFE      Restrictive code access permissions, no access to external files/networks.
   PERMISSION_SET EXTERNAL_ACCESS Allow some external system resources, files/networks/Registry
   PERMISSION_SET UNSAFE unrestricted access to resources

Permissions:
CREATE ASSEMBLY
EXTERNAL ACCESS ASSEMBLY for PERMISSION_SET = EXTERNAL_ACCESS
SYSADMIN fixed server role for PERMISSION_SET = UNSAFE

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.

When accessing client_assembly_specifier, SQL Server will impersonate either the security context of the current Windows login or the security context of the SQL Server service account.

Example

CREATE ASSEMBLY MyDemo
FROM 'C:\democode\HelloWorld.dll'
WITH PERMISSION_SET = SAFE;

"Until all the powerful are just, the weak will be secure only in the strength of this Assembly" - John F. Kennedy, address to the UN General Assembly

Equivalent Oracle command:

CREATE PACKAGE/BODY

Simon Sheppard
SS64.com