How to list all CLR objects and associated procedures

07 November,2014 by Jack Vamvas

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

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to list all CLR objects and associated procedures


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer