How to list CLR objects inside a SQL Server database

24 November,2017 by Jack Vamvas

Troubleshooting an error message An error occurred in the Microsoft .NET Framework while trying to load assembly id xxx and needed to identify  CLRs related to a particular database.

This query lists the CLR objects inside a SQL Server database. Use the information in this query to start diagnosing the error messages.

 

use myDB
go
SELECT o.object_id AS [object_ID]
      , a.assembly_id AS [assemblyID]
	 ,a.name AS [assemblyName]
    ,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName]
    ,o.type_desc AS [CLRType]
    ,o.create_date AS [DateCreated]
    ,o.modify_date AS [SateModified]
    ,a.permission_set_desc AS [CLRPermission]
	
FROM sys.objects o
INNER JOIN sys.module_assembly_usages ma
    ON o.object_id = ma.object_id
INNER JOIN sys.assemblies a
    ON ma.assembly_id = a.assembly_id

There are different reasons for CLR error messages.Here are some  links to posts which have more queries and troubleshooting methods for CLR related problems

Troubleshooting SQL CLR memory pressure

How to list all CLR objects and associated procedures

How to get the DNS API permissions in CLR assembly

 

A quick note on setting the database setting TRUSTWORTHY to ON. By default databases are set as TRUSTWORTHY is OFF. A common solution you'll see on forums and other blog posts is to set the option to ON, that way if you have an assembly with a permission set of UNSAFE and the owner of the database has elevated authority - such as sysadmin then it is possible to create assemblies with a permission of UNSADE and execute the assemblies.

DON'T just follow this advice blindly. As a guide manage trust on a application specific basis .

 

 

 


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 CLR objects inside a SQL Server database


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