How to list all CLR objects and associated procedures

A developer reported   an error in the Microsoft .Net Framework when executing a CLR object.

Use the query and CLR  views  to understand and troubleshoot CLR integration managed database objects that are executing in the SQL Server instance.

An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx
System.IO.FileLoadException: Could not load file or assembly 'myapp.database.sqlserver, Version=2.1.40317.0, Culture=neutral, PublicKeyToken=xxxxxxxx' or one of its dependencies. An error relating to security occurred.

 How to troubleshoot .Net Framework errors and CLR objects

These type of errors can be difficult to troubleshoot.  One of the reasons they can be difficult is finding the CLR objects and the associated procedures.  Use this query to list the CLR objects and associated procedures

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class, 
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

Once you’ve identified the CLR objects , begin troubleshooting.  There can be a number of reasons for the error. The most common reason I’ve seen is the AUTHORIZATION between the CLR and database is misaligned.  Part of the troubleshooting process should include understanding how the authorization occurs between the objects.

Workarounds to AUTHORIZATION issues

A quick workaround is to change  the AUTHORIZATION   level of the database.  An example of the code is:

 ALTER AUTHORIZATION ON DATABASE::[MYDB] TO sa;

 Read more on ALTER AUTHORIZATION sql to change owner of a database

The sys.dm_clr_appdomains  view creates a recordset of the application domains on the server.

 SELECT * from sys.dm_clr_appdomains

The sys.dm_clr_loaded_assemblies  view creates a recordset for each managed user assembly in the server address space.

 SELECT * from sys.dm_clr_loaded_assemblies

Read More CLR Assembly and SQL Server Security

How to get the DNS API permissions in CLR assembly

SQL Server Security Policy

How to create a SQL Server Security Audit

Powershell sql server security audit