I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code inside each one.

Is there a best way to do this from an administrative and security standpoint? The CLR functions are very basic like string breakers, email validation, url en/decode, base64, and etc. I would like only the dbo schema in each database to be able to access the functions.

  1. Is there any simple way to do this?
  2. Also I am not clear if the CLR dll is embedded and if I move the database, it tags along, or do I have to move the dll as well.


  • 1
    OK, sounds good. As long as I don't hear crickets from the dead silence about the question. Always had luck at stackoverflow
    – Alex Erwin
    Aug 14, 2012 at 16:22
  • 1
    dba.se is less frenzied than SO, but I'm confident you'll get good attention on a question like this within a day or so. Are you happy to be patient that long? Aug 14, 2012 at 16:37
  • Lol, patience is a virtue. I have a fair amount of it, and the question itself, I would think, MS would address. What if you are a SQL Server host that wants to expose some useful functions but not have your server open to CLR in full force?
    – Alex Erwin
    Aug 14, 2012 at 17:11

3 Answers 3


At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time.

A couple of months back our data center got flooded - filling several servers full of water. When I rebuilt them I only used the backups of the db that had been taken the night before. So far we have had no issues.. (touch wood!)

I am not sure if this is the right thing to do from a security perspective but the way we grant access to the CLR procs etc is to create a role within the shared database, and then add users from other databases to that role. The role is then granted execute on the CLR procs.

There may be access issues if the CLR is trying to do things like access resources outside of the database that it is contained within but you can set the permission on the assembly when you create it. The below link has a lot more information regarding permissions than I can explain here though:


I hope that this helps you.


The assembly binary is stored as a blob in the database, so it's carried wherever the database goes. CLR is only enabled on the instance -- there are no database-specific settings for that.

In any event, why are you trying to do this?

(I'm not trying to be argumentative; I just want to hear the motives involved, because perhaps the problem could be solved in a different way that meets your needs.)

There's no way to do this easily, except to put the assembly in a shared database.

That said, I would think it's advantageous to embrace the database-centric architecture, unless there's a particular situation that has very compelling reasons to centralize. The reason why is that putting the assembly (or anything for that matter) outside the database creates a dependency in your environment. This is precisely the opposite approach Microsoft is building towards with Contained Databases starting in SQL Server 2012.

  • When you start needing to use features like replication or clustering, this dependency can potentially add a tremendous amount of complexity to the deployment, but also to troubleshooting, and failover procedures.

  • This architecture is much less obvious to people unfamiliar with the system (i.e., it's less self-discoverable, and less self-documenting).

  • If you end up requiring different security in different databases, or anything that involves variation, you're in a world of hurt.

  • If these databases get deployed to customers (sounds like they won't be, but I'll say this for completeness), this adds complexity to the deployment procedure, maintenance, and troubleshooting.

  • Since all databases would share this code, if any bugs are introduced (or fixed!), this could potentially break all the applications that rely on the databases. Comprehensive unit testing would be an absolute must.

If you have several databases that need the same functionality, there are other ways of reducing the amount of duplication involved, which I assume is the point of the exercise. Even a fairly complex CLR assembly won't take up much physical storage space compared to the data in the database itself (almost always), so I don't see that as a valid argument unless you have literally thousands of tiny databases that need this assembly.

What you could do is modify other parts of the deployment procedure for these databases to reduce the source duplication. For example, build and deploy the assembly from the common location of the CLR code in source control. Or, create a script that deploys the same assembly to the databases. Automate this part of things as much as possible, and it won't be a big deal.

I agree that what I'm suggesting is a tradeoff, because there will still be some duplication, but that has to be balanced with the negatives involved with implementing an architecture that doesn't follow the prescribed standard. Only you can decide what's right for your environment.

  • I see your point with possibly breaking things, however it is to simplify roll outs of code. No army of developers here. Just me. I do however have others using the database, so I need to make sure the functions are inaccessible unless used from stored procedures I define.
    – Alex Erwin
    Aug 15, 2012 at 9:03

As the other two answers correctly state, Assemblies are loaded into a particular database and are not system-wide (though I'm fairly certain that the assembly_id value is unique system-wide). This means that they are backed up, and restored, with each database that they are loaded into.

Also, the enabled / disabled setting of CLR Integration (via sp_configure) is system-wide. As a side note, that setting is only for user-created CLR functionality; CLR in a general sense is always enabled as certain built-in functionality depends on it.

That said, while the other two answers here do make valid points, those points are not SQLCLR-specific, and there is no mention of the factors in making this decision that are specific to SQLCLR code. There are memory issues to consider if you deploy code to each individual database (assuming you have many databases), potential resource contention issues, potential security-related issues, etc.

I have provided what should be a comprehensive list of things to keep in mind, specifically for SQLCLR code, when deciding between a centralized database vs individual database deployment. Rather than duplicate the list here, please see the following answer (also here on DBA.SE):

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

Also, on a related note, I would question why any database is being set to TRUSTWORTHY ON. The functionality noted in the Question (i.e. "string breakers, email validation, url en/decode, base64, etc") is all possible within a SAFE Assembly. You should not use either the EXTERNAL_ACCESS or UNSAFE perission_set values unless absolutely necessary. And if it is necessary for some number of functions, then those should be in a separate Assembly that contains only SAFE code such that any scalar functions that do not do data access and are marked as IsDeterministic = true will be able to make use of the performance benefit of being able to participate in parallel plans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.