You are database developer on an instance of SQL Server 2008. The development team has recently created and compiled an assembly using a .NET Framework language. The assembly includes a method that requires access to the file system. You want to allow a user to access the method directly from Transact-SQL.
Which action should you take?
A. Set the TRUSTWORTHY database property to OFF.
B. Grant the user access to the stored procedure using a database role that has elevated privileges.
C. Grant the user the db_accessadmin fixed database role.
D. Register the assembly with the EXTERNAL_ACCESS permission.
Correct Answer: D
Explanation/Reference:
When you register a .NET assembly using the CREATE ASSEMBLY statement, you can specify a permission set that identifies the actions that can be performed.
You may specify one of the following permission sets depending on which actions should be allowed:
SAFE: Provides access to the current database, but does not allow the function to access external files, the registry, or environment variables, or to call unmanaged code.
EXTERNAL_ACCESS: Provides access to the current database and external resources, including the file system, registry, environment variables, and unmanaged code.
UNSAFE: Provides full trust access, and the CLR does not perform any permission checks.
The default permission set is SAFE. In this scenario, the assembly needs to access the file system. Therefore, you should specify a PERMISSION_SET of EXTERNAL_ACCESS. For example, you might use the following statement to register an assembly that is allowed to access the file system:
CREATE ASSEMBLY MyAssemblyFROM ‘C:Test.dll’ WITH PERMISSION_SET = EXTERNAL_ACCESS;
After you register the assembly, you can use a CREATE PROCEDURE statement with the EXTERNAL NAME clause to specify the name of the method the user needs to access, and the user will be able to access the CLR stored procedure directly from Transact-SQL.
You should not set the TRUSTWORTHY database property to OFF. The TRUSTWORTHY database property should be set to ON to allow the assembly to access external resources, such as the file system.
You should not grant the user access to the stored procedure using a database role that has elevated privileges. There is no need to create a special database role to provide access to the file system. You can accomplish this using the permission set of the assembly.
You should not grant the user the db_accessadmin fixed database role because this will not allow the function to access the file system. This would also allow the user to perform actions that are not desired, such as creating a schema or adding or removing users. Granting fixed database roles should be avoided.