I would like to use code that I developed in C Sharp 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 standpoi...
@JackDouglas It's databasey. We implemented plenty of CLR at my last job and we only ever implemented one central copy of the CLR assemblies. You can create wrappers that EXECUTE AS to avoid the trustworthy bit, but personally I don't understand all of the objection to trustworthy in the first place. And we had so much cross-db crap going on (mostly service broker) that we just turned on trustworthy anyway.
As for the backup / restore, the DLL is stored in the file system, not the database, so yes the DLL has to move. If the file path differs, then it needs to be updated, though I've never done that so I'm not sure how to re-register an assembly only updating the path.
Unfortunately I don't have time to craft a proper response now (headed to a Dr's appt) but if anybody wants to take my text here and form some kind of answer or comment, I won't object.
@JackDouglas Is this a topic y'all are particularly interested in? Sounds like he is primarily interested in the logistics of maintaining such a library, not so much actually writing it, which would point in your direction.
It seemed like much of the logistics he's interested in are how to implement the CLR library from a security perspective inside of SQL Server.
e.g. how to let databases call a central CLR assembly without turning on the trustworthy bit. That seems to have very little at all to do with source control.
