Quick Scan Report – User-Defined Assemblies

What this check looks for

Assemblies in each database where is_user_defined = 1, reported with the database name, the assembly name and its permission set. The is_user_defined column was added in SQL Server 2008, so on older builds the check uses a different filter to exclude the Microsoft assemblies.

Why it matters

A CLR assembly is compiled .NET code running inside the SQL Server process. Whether that is fine or alarming depends almost entirely on one column: the permission set.

Permission set What the code can do
SAFE Computation and local data access only. No file system, no network, no registry.
EXTERNAL_ACCESS Files, network, registry, environment variables, as the service account.
UNSAFE Anything, including unmanaged code and calls that can destabilize the process.

SAFE is genuinely safe, and a SAFE assembly doing string manipulation or a regular expression match is a reasonable thing to have. It is faster than the T-SQL equivalent for that kind of work and it cannot reach outside the engine.

EXTERNAL_ACCESS and UNSAFE are a different matter, because they run as the SQL Server service account. Combine that with a service account running as LocalSystem and you have code in the database with full control of the Windows server. This check and the service account check are worth reading together for exactly that reason.

The specific concerns:

  • Security. UNSAFE code can call unmanaged APIs, which means it can do anything the process can do. It is the same reach as xp_cmdshell, without appearing in the configuration.
  • Stability. UNSAFE code can corrupt the SQL Server process address space. An access violation in a CLR assembly can take the instance down, and those dumps are difficult to attribute.
  • Memory. CLR memory is allocated outside the buffer pool, so an assembly with a leak consumes memory SQL Server has not accounted for and does not reclaim.
  • Provenance. The finding often surfaces assemblies nobody recognizes. A DLL loaded into a database five years ago by a developer who has left, with no source in version control, is a real operational problem regardless of what it does.
  • Upgrades. Assemblies built against old .NET Framework versions can block or complicate a SQL Server upgrade, and clr strict security in SQL Server 2017 and later changed the rules enough to break assemblies that worked before.

clr strict security deserves its own mention. From SQL Server 2017, every assembly is treated as UNSAFE for permission purposes regardless of its declared permission set, so all of them must be signed and have a corresponding login with UNSAFE ASSEMBLY permission, or be explicitly trusted. Instances upgraded to 2017 or later frequently have assemblies that stopped loading, or a TRUSTWORTHY database set as a workaround, which is a worse problem than the one it solved.

How to confirm it yourself

Every user assembly on the instance:

EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4
SELECT DB_NAME()                AS [database_name],
       a.[name]                 AS [assembly_name],
       a.[permission_set_desc],
       a.[clr_name],
       a.[create_date],
       a.[modify_date],
       a.[is_visible]
  FROM sys.assemblies AS a WITH (NOLOCK)
 WHERE a.[is_user_defined] = 1;';

Sort your attention by permission_set_desc. SAFE is a documentation item. UNSAFE is a security review.

What each assembly actually exposes:

SELECT a.[name]                AS [assembly_name],
       a.[permission_set_desc],
       am.[assembly_class],
       am.[assembly_method],
       o.[name]                AS [sql_object_name],
       o.[type_desc]           AS [sql_object_type]
  FROM sys.assembly_modules AS am WITH (NOLOCK)
 INNER JOIN sys.assemblies  AS a  WITH (NOLOCK) ON a.[assembly_id] = am.[assembly_id]
 INNER JOIN sys.objects     AS o  WITH (NOLOCK) ON o.[object_id]   = am.[object_id]
 WHERE a.[is_user_defined] = 1
 ORDER BY a.[name];

That tells you which stored procedures, functions and triggers are backed by CLR code, which is what you need before removing anything.

Whether anything calls them, which decides whether this is cleanup or a change project:

SELECT OBJECT_NAME(ps.[object_id]) AS [object_name],
       ps.[execution_count],
       ps.[last_execution_time],
       ps.[total_worker_time] / 1000 AS [total_cpu_ms]
  FROM sys.dm_exec_procedure_stats AS ps WITH (NOLOCK)
 WHERE ps.[database_id] = DB_ID()
   AND OBJECT_NAME(ps.[object_id]) IN
       (SELECT o.[name] FROM sys.assembly_modules AS am
         INNER JOIN sys.objects AS o ON o.[object_id] = am.[object_id])
 ORDER BY ps.[execution_count] DESC;

That is since the last restart only, so an assembly with no entries may still be used monthly. Check over a longer period before concluding it is dead.

The instance settings that govern all of this:

SELECT [name], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('clr enabled', 'clr strict security');

And the TRUSTWORTHY workaround, which is worth finding if it is there:

SELECT [name], [is_trustworthy_on], [owner_sid],
       SUSER_SNAME([owner_sid]) AS [database_owner]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_trustworthy_on] = 1 AND [database_id] > 4;

TRUSTWORTHY on a database owned by a sysadmin is a privilege escalation path, and it is the most common shortcut used to make assemblies load after an upgrade.

How to fix it

Inventory first, then decide per assembly. Do not start by disabling CLR.

  1. Account for every assembly. For each one: what does it do, who wrote it, where is the source, and is it still called. An assembly you can explain is not a finding; one nobody can explain is.
  2. Get the source into version control. If it does not exist, extract the binary while you still can:
SELECT a.[name], af.[name] AS [file_name], af.[content]
  FROM sys.assembly_files AS af WITH (NOLOCK)
 INNER JOIN sys.assemblies AS a WITH (NOLOCK) ON a.[assembly_id] = af.[assembly_id]
 WHERE a.[is_user_defined] = 1;

That gives you the DLL bytes, which can at least be decompiled if the source is gone.

  1. Reduce the permission set where you can. Most assemblies declared EXTERNAL_ACCESS or UNSAFE do not need to be:
ALTER ASSEMBLY [YourAssembly] WITH PERMISSION_SET = SAFE;

If it genuinely needs external access, the statement fails and you have your answer.

  1. Remove what is unused:
-- drop the SQL objects that reference it first
DROP FUNCTION [dbo].[clr_SomeFunction];
DROP ASSEMBLY [YourAssembly];

DROP ASSEMBLY fails while any object references it, which is a useful safety net.

  1. Turn CLR off entirely if nothing uses it:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 0;
RECONFIGURE;

Check every database first. Disabling CLR breaks every assembly backed object on the instance immediately.

  1. Fix the TRUSTWORTHY workaround properly if you find one. The correct approach is to sign the assembly and create a login from the certificate with UNSAFE ASSEMBLY permission:
USE [master];
CREATE CERTIFICATE [YourAssemblyCert] FROM FILE = 'C:\certs\YourAssembly.cer';
CREATE LOGIN [YourAssemblyLogin] FROM CERTIFICATE [YourAssemblyCert];
GRANT UNSAFE ASSEMBLY TO [YourAssemblyLogin];

-- then turn the shortcut back off
ALTER DATABASE [YourDatabase] SET TRUSTWORTHY OFF;

Leave clr strict security on. Turning it off to make an old assembly load reintroduces the behavior that was tightened for good reason.

How long it takes

About half an hour to inventory and assess. Replacing or removing an assembly that is still in use is an application change with its own timeline.

Report Why you would go there
Security Posture CLR alongside the rest of the security surface.
Configuration Values clr enabled and clr strict security.
Databases Every database, to check each one for assemblies.
Memory Usage CLR memory, which sits outside the buffer pool.
Schema Search The procedures and functions backed by assemblies.
Check
SQL Server running as local system What an UNSAFE assembly inherits.
xp_cmdshell is enabled The same reach through a different door.
TRUSTWORTHY database The workaround that often accompanies this.
Database ownership issues Why TRUSTWORTHY is an escalation path.
Ole Automation Procedures enabled Another way code reaches outside the engine.

Frequently asked questions

Is CLR itself a problem? No. A SAFE assembly is genuinely contained and can be the right tool for string and computation heavy work. The concern is EXTERNAL_ACCESS and UNSAFE, and assemblies nobody can account for.

We upgraded and our assemblies stopped working. That is clr strict security, introduced in SQL Server 2017. Sign the assembly and grant UNSAFE ASSEMBLY to a certificate based login rather than turning the setting off or setting the database TRUSTWORTHY.

Can I see what the assembly does without the source? You can extract the binary from sys.assembly_files and decompile it. That is worth doing for any assembly whose source has been lost, and the extraction should happen before anybody considers dropping it.

Nothing has called it since the last restart. Procedure stats reset at restart, so that only proves it has not run recently. Check over a period that covers your monthly and quarterly processes before treating it as dead.