This is a question that was out today at the SQL Server 2005 Meeting in Microsoft and that is connected to what I've said in the past about CLR integration into SQL Server 2005: what are the CLR advantages against Extended Stored Procedures? And what about security?
Extended stored procedures (normally with the xp_ prefix) calls external DLL (physically reside in a DLL file) that are built with unmamaged code (C, C++) and, as you know, this could cause some security problem on the system (imagine a wrong pointer on memory...).
CLR integration is more secure because the code is managed and integrated into the SQL Server 2005 engine by using CAS (Code Access Security), and it's compiled in native code before the execution (so extremely fast because it's adapted to your machine).
Regarding security, you've the choice to grant the security level you want to your CLR code embedded into SQL Server.
If you've created (as said in the previous post) an assembly MyProcedure.dll, you can integrate it into SQL Server 2005 simply by using this query:
CREATE ASSEMBLY MyProcedure
FROM 'C:\MyProcedure.dll'
WITH PERMISSION_SET=SAFE
The clause WITH PERMISSION_SET permits you to grant the security level you want for your CLR code. The permitted level are:
- SAFE: the most secure level, you grant access only to the local data (you cannot execute remote calls etc.)
- EXTERNAL_ACCESS: it permits the access also to system variables, registry, networks etc. Use it with caution (the context used is the user rights for the MSSQLServer service).
- UNSAFE: it grants full access to all the resources. I recommend to NEVER grant this, remember that it's a database system.
Be careful... if you use the CLR, remember these security rules. 