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?
2
-
No, this is an instance level option. There is no option to enable it for specific databases.Martin Smith– Martin Smith2016-06-10 09:56:39 +00:00Commented Jun 10, 2016 at 9:56
-
1Possible duplicate of Is “CLR Integration” enabled at a database level or the server/instance level?.Solomon Rutzky– Solomon Rutzky2016-06-10 12:18:59 +00:00Commented Jun 10, 2016 at 12:18
Add a comment
|
1 Answer
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.
2 Comments
Bacon Bits
Of course this won't prevent a login in the sysadmin role from disabling the trigger, but if that's the problem you have then your problem is not really a technical one anymore.
Solomon Rutzky
@BaconBits True and True. Though, there is no way really to prevent a
sysadmin from doing anything: turning on/off "CLR Integration" or re-granting the CREATE ASSEMBLY had that worked. I think the benefit here is that at least something like an automated rollout script (that runs as sysadmin) would be prevented, where it would be allowed if only DENYing CREATE ASSEMBLY to Public.