SQLCLR Permission Set Levels
SQL Server provides three permission set levels when you load an assembly into a database using the CREATE ASSEMBLY statement: SAFE, EXTERNAL_ACCESS, and UNSAFE. These are the permission sets that form the AppDomain policy level as shown in Figure 3 and Figure 5.

Here is a typical statement that installs an assembly within the FileLoader.dll file and gives it the EXTERNAL_ACCESS permission set.

   CREATE ASSEMBLY FileAccess
   FROM 'E:\FileLoader.dll'
   WITH PERMISSION_SET = EXTERNAL_ACCESS
   GO

Each of the three permission set levels confers a different set of CAS permissions on the code when it executes. I'm indebted to Nicole Calinoiu, my fellow Visual Developer - Security Microsoft MVP, for the explorations that enabled me to flesh out some of the detail in the following discussion about specific permissions granted at each level. You can read more detail about what she discovered about SQLCLR permission sets in her account in the Resources sidebar.

SAFE
SAFE is the default permission set. It grants just enough permissions to execute the code, do internal computations that don't access external resources, and access the data and objects within the host SQL Server instance. SAFE code cannot access external resources, so it can't read or write disk files, can't access any other SQL Server instances, or read or write to the registry. The code must also be verifiably type safe, which helps protect against various attacks including buffer overruns.

SAFE code is the most reliable and secure SQLCLR code. It can do pretty much whatever code written in T-SQL can do within the database and server instance. It grants the CAS permissions listed in Table 1. As you can see in the table, the code is able to run and is able to read objects and data in the host SQL Server instance, using a special form of an ADO.NET connection string, either "context connection=true" or "context connection=yes." Any other connection string will cause a security exception.

Table 1: Permission set granted to SAFE assemblies.

Permission	Type	Restriction, if any
SecurityPermission	Restricted	Execution
SqlClientPermission	Restricted	No blank password, context connection string only

The resulting permission set granted to an assembly is the permissions listed in Table 1 intersected with those from the enterprise, machine, and user level. Because those levels by default have all permissions, the assembly receives only those listed in Table 1. Make sure you understand that.

EXTERNAL_ACCESS
The EXTERNAL_ACCESS permission set is a big step up from SAFE in that it allows restricted access to resources outside of the SQL Server instance, including disk files, the data and objects in other SQL Server instances, environment variables, and some parts of the registry. Access to these other resources is usually in the security context of the SQL Server service account, but the code can impersonate other users to get access. This level grants the permissions listed in Table 2.

Table 2: Permission set granted to EXTERNAL_ACCESS assemblies.

Permission	Type	Restriction, if any
EnvironmentPermission	Unrestricted	—
FileIOPermission	Unrestricted	—
RegistryPermission	Restricted	Read only access to HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE, HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG, and HKEY_USERS
SecurityPermission	Restricted	Assertion, Execution, SerializationFormatter, ControlPrincipal
KeyContainerPermission	Unrestricted	—
SqlClientPermission	Unrestricted	—
EventLogPermission	Restricted	Only on local machine, by Administrators only
DnsPermission	Unrestricted	—
SocketPermission	Restricted	IP address only
WebPermission	Restricted	Access local host only via HTTP
SmtpPermission	Restricted	Connect access only
NetworkInformationPermission	Restricted	Ping access only
DistributedTransactionPermission	Unrestricted	—
StorePermission	Unrestricted	—

The unrestricted FileIOPermission might look a little scary, since it means that from the CLR's perspective the code can access any location on disk. But keep in mind that the code still is running with the operating system security of the local service account. So if that account can't access a file, the SQLCLR code won't be able to either.

The local service account is typically a very powerful account, so there is the possibility of abuse. The moral is to give access to these assemblies only to logins that you'd trust with the service account and don't use the local system account as the service account for SQL Server.

It is interesting to note that one thing you can do with EXTERNAL_ACCESS is to use a more traditional ADO.NET connection string to connect to a database in the same SQL Server instance in which the SQLCLR code is running. This requires the SqlClientPermission so that you can use a connection other than the "context connection" string required to read the data in the current instance, specifying the usual server name, credentials, and so on. I haven't figured out a reason yet why you'd want to do this, but it's good to have options, right?

UNSAFE
The UNSAFE permission set is the SQLCLR equivalent of full trust, in which the CLR suspends all permissions checking. It receives a single, unrestricted SecurityPermission permission, which is the CLR's way of granting full trust.

An UNSAFE assemblies can potentially do all kinds of nasty things because it's inherently highly trusted code. For example, it can call unmanaged code, such as COM components and the raw Win32 API. It is still subject to operating system permissions of the service account, but the CLR won't restrict its ability to access any resources.

Because UNSAFE is so, well, unsafe, only a sysadmin can create this kind of assembly.