You are a database developer. You plan to design a database solution by using SQL Server 2008. You configure a database on a server to use a common language runtime (CLR). You need to create a CLR assembly that enables the CLR stored procedure to access environment variables available on the server. You also need to ensure that the CLR assembly has the minimum permissions assigned.
What should you do?
A. Enable the TRUSTWORTHY database property.
B. Create the assembly by using the SAFE permission set.
C. Create the assembly by using the UNSAFE permission set.
D. Create the assembly by using the EXTERNAL ACCESS permission set.
Correct Answer: D
Explanation/Reference:
Syntax:
CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { <client_assembly_specifier> | <assembly_bits> [ ,…n ] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
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.
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.
The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement. For example, ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;
