Registering CLR Assemblies for Use with SQL Server

10/13/2010 9:38:34 AM
Assemblies are physical units of deployment that contain managed code. This managed code can be called by CLR stored procedures, CLR functions, CLR triggers, CLR user-defined aggregates, and CLR user-defined data types. Assemblies are stored externally from SQL Server and thus can be used by other applications. When called by SQL Server, the code within the assembly is loaded into the SQL Server service process and is executed within its context.

Before you can use an assembly from SQL Server you must register it. When you register the assembly, it is imported into SQL Server and configured for use. Often assemblies depend on other assemblies, which in turn depend on others, and so on, forming a dependency tree. During registration, the entire chain of dependencies for your assembly must be imported into SQL Server. Assemblies are registered using the CREATE ASSEMBLY statement as shown in Example 4.1.

Example 1. Syntax—Create Assembly
CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { <client_assembly_specifier> | <assembly_bits> [,...n ] }
[ ; ]

The assembly_name is the unique name of the assembly within SQL Server. To avoid confusion, name your assemblies after their file names, unless there is a naming conflict with another registered assembly. For example, if you want to use the assembly MyAwesomeFunctions.dll, designate MyAwesomeFunctions as its name when registering it.

The owner_name is the role or user who will become the owner of the assembly. Registered assemblies have owners within SQL Server, much like tables, stored procedures, and other database objects. Note that when registering the assembly, you can only specify a role of which you are a member, or a user from whom you have permission to impersonate. If you don’t specify the owner_name explicitly, you will be registered as the owner by default (again, the same principle as tables or stored procedures). Just like other objects, it is recommended to register all assemblies as owned by dbo. This will help avoid major interoperability problems that arise when assemblies that need to call each other are owned by different users.


Be careful when choosing anything but dbo as the AUTHORIZATION parameter for CREATE ASSEMBLY. You must have a very specific reason not to use dbo, for example, if the calling user is not a member of db_owner. The best practice is to always use dbo for all assembly ownership.

The client assembly specifier is the local or network path of the physical assembly DLL file you wish to register. Alternatively, you can specify the actual bits of the assembly as the assembly bits instead of its network path. The bits are specified as either a literal varbinary value or an expression of a varbinary data type. You must first specify the bits for the root assembly and then all its dependencies.


Remember that when SQL Server registers the assembly, it imports the assembly file as well as all its dependencies. If any dependency is not available, and it is not already registered with SQL Server, the registration statement will fail and report an error.

Also note that if a dependency is already registered, but under a different owner, your statement will also fail.

The PERMISSION_SET parameter specifies what security permissions are granted to the assembly code when it executes within SQL Server. We will discuss permission sets next in this chapter.

The following example registers the MyAwesomeFunctions assembly, owned by dbo;

CREATE ASSEMBLY MyAwesomeFunctions
FROM 'C:\Valentine\MyAwesomeFunctions\bin\Debug\MyAwesomeFunctions.dll'

For this statement to succeed, the calling user must be a member of db_owner role, and also have permission to access the MyAwesomeFunctions.dll file. All dependencies for the assembly must be available for SQL Server to import or must have been already registered with SQL Server. Remember, you don’t need to register supported assemblies as they are registered by default.


You cannot register an assembly if there is already another version of this assembly registered with the same name, public key, and culture (locale). The public key is used to generate a unique name for an assembly. For example, you cannot register MyAwesomeFunctions version 1.5.0 and MyAwesomeFunctions version 1.5.2.

Finally, you can unregister an assembly using the DROP ASSEMBLY statement and change the properties of an assembly using the ALTER ASSEMBLY statement. Supported assemblies that are loaded by default cannot be altered or dropped.

PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer
Game of War | Kate Upton Commercial