Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

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.

• Is there any simple way to do this?

• 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.

Thanks

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:

http://msdn.microsoft.com/en-us/library/ms345101.aspx

I hope that this helps you.

StackExchange Database Administrators Q#22460, answer score: 8