There are a number of different ways that SQL Server 2005 checks to see whether on not you're trying to catalog or execute dangerous .NET code. This checking is enforced by SQL Server at CREATE ASSEMBLY time and also at execution time.
During CREATE ASSEMBLY, SQL Server reflects on the assembly and check for certain coding practices than can "cause problems" at runtime. It's well-known that SQL Server does not allow you to catalog an asssembly as SAFE or EXTERNAL_ACCESS if the assembly contains finalizers or mutable statics.
It's also well-known that SQL Server will throw a SecurityException at runtime if you attempt to access classes or methods marked with a Host Protection Attribute. HostProtectionAttribute was added to the .NET 2.0 runtime to allow class-library authors to annotate their code in order to inform the host (in this case SQL Server) of behaviors such as "MayLeakOnAbort" or "SelfAffectingThreading". HPAs provide protection at execution time, not catalog time.
It's a little-known fact that SQL Server also checks for the presence of certain attributes on assembly code at CREATE ASSEMBLY time. If your code contains dangerous attributes CREATE ASSEMBLY does not succeed. This is different checking than what HPAs allow. This list of "dangerous attributes" is checked at catalog time. The attributes SQL Server considers dangerous include:
System.STAThreadAttribute and System.MTAThreadAttribute
Doubtless there are others. Attempting to catalog an assembly that contains one of these attributes produces an error message like this:
Msg 6237, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ProcWithAttr' on type 'StoredProcedures' in assembly 'SomeAssm' has invalid custom attribute 'System.STAThreadAttribute'.
Interestingly, I was able to find one that slipped by; SQL Server will allow cataloging an assembly with the UnverifiableCodeAttribute as SAFE. Maybe it was due to the fact that my module did not actually *contain* unverifiable code, just an attribute that said it did.
I'm amazed at the thoroughness with which SQL Server checks for code that can cause it problems as a .NET runtime host. .NET reflection is truly its friend; try to implement this type of checking on an extended stored procedure!