Professional SQL Server™ 2005 Programming

14.3. Uploading Your Assembly to SQL Server

That's right — I used the term upload. When you "create" an assembly in SQL Server, you're both creating a copy of the DLL within SQL Server as well as something of a handle that defines the assembly and the permissions associated with it.

CREATE ASSEMBLY <assembly name>
[ AUTHORIZATION <owner name> ]
FROM { <client assembly specifier> | <assembly bits> [ ,...n ] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]

The CREATE portion of things adheres to the standard CREATE <object type> <object name> notion that we've seen throughout SQL. From there, we have a few different things to digest:

Option	Description
AUTHORIZATION	The authorization is the name of the user this assembly will be considered to belong to. If this parameter is not supplied, then the current user is assumed to be the owner. You can use this to alias to a user with appropriate network access to execute any file actions defined by the assembly.
FROM	The fully qualified path to the physical DLL file. This can be a local file or a UNC path. You can, if you so choose, provide the actual byte sequence to build the file right on the line in the place of a file (I have to admit I've never tried that one).
WITH PERMISSION_SET	You have three options for this. SAFE is the default and implies that the object is not utilizing anything that requires access outside of the SQL Server process (no file access, no external database access). EXTERNAL_ACCESS indicates that ...