Search code examples
sql-serversqlclr

How to enable CLR integration for just a specific database in Microsoft SQL Server?


CLR integration is disabled by default. My understanding of the instructions to enable it implies that it is enabled or disabled for the entire server. Is it possible to enable it on a per-database basis? If so, how?


Solution

  • Enabling "CLR Integration" is a Server/Instance-level only option; it cannot be handled separately per Database.

    However, while I am not sure why it would matter to handle this on a per-Database basis, you could always DENY CREATE ASSEMBLY in Databases where you do not want custom SQLCLR code being used.

    UPDATE

    I just tested DENY CREATE ASSEMBLY TO [Public]; and this does not work for Logins that are a member of the sysadmin fixed server role. So another way to effectively deny permission to an action is to trap the action via a DDL Trigger and issue a ROLLBACK which will cancel the action.

    The following is a DDL Trigger that works in whatever Database it is created in and traps both CREATE ASSEMBLY and ALTER ASSEMBLY statements, but still allows DROP ASSEMBLY (using DDL_ASSEMBLY_EVENTS as the Event Group would disallow all three actions):

    USE [db_name];
    GO
    CREATE TRIGGER [PreventCreateAssembly]
    ON DATABASE
    AFTER ALTER_ASSEMBLY, CREATE_ASSEMBLY -- DDL_ASSEMBLY_EVENTS
    AS
    
    RAISERROR(N'Cannot CREATE or ALTER Assemblies!', 16, 1);
    ROLLBACK;
    GO
    

    With this DDL Trigger in place, when I tried to create the Assembly, I now receive the following error, even though my Login is a sysadmin:

    Msg 50000, Level 16, State 1, Procedure PreventCreateAssembly, Line 59
    Cannot CREATE or ALTER Assemblies!
    Msg 3609, Level 16, State 2, Line 54
    The transaction ended in the trigger. The batch has been aborted.