My company recently upgraded to SQL Server 2005. I'm the DBA, and I'm concerned about enabling the SQL Server Common Language Runtime (SQLCLR). I know that by default, the SQL Server clr enabled option is off. Is it an all-or-nothing proposition? I'm concerned about losing control of the applications running on the server because I'm not proficient with using .NET-based languages.
Entire books have been written about this topic, so this answer isn't comprehensive. But here's some information that might help you. First, as you know, SQL Server's ability to create and run CLR-based procedures is turned off by default. Any member of the sysadmin role can turn on the clr enabled option, but if other database users in the sysadmin role want to create CLR-based procedures, the DBA must grant them the create assembly database-level permission.The create assembly procedure also lets you set the level of code-access permission (i.e., Safe, External_ Access, or Unsafe) when you create the specified assembly. The create assembly routine looks like this:
CREATE ASSEMBLY
[ AUTHORIZATION ]
FROM {
|
[,...n ] }
[ WITH PERMISSION_SET = { SAFE |
EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]
When you create assemblies that access external resources, you must set the WITH PERMISSION_SET parameter to the EXTERNAL_ACCESS permission level. Note that only members of the sysadmin fixed server role can set assembly code-access permission to unsafe. After a developer creates an assembly, you manage the process of granting permissions to execute the assembly as if it were a normal procedure. If you set the appropriate permission levels I've described, you won't lose control of the code running on the server. For more information about the SQLCLR, read the SQL Server 2005 Books Online (BOL) topic," Administering Database Objects with Common Language Runtime (CLR) Integration"at http://msdn2.microsoft.com/kbid=ms131074.aspx.