Using CLR User Defined Functions in SQL Server
This is the classic argument, for or against stored procedures in general. The fact is, you can't stop people connecting to your database. Sure Joe in the warehouse will use the in-house stock control app that has all your business rules in, he's got a dumb terminal/web browser on the Intranet, he's got no choice. But if Bob the CFO wants to connect with Excel and do some pivot tables, are you going to say no, you can't do that, give me 6 months to write you a custom app in the application server tier? No. Or Jane SQL consultant who likes a command line client, and you're on vacation so you can't tell her no.

But you don't want to risk any integrity issues so put your validation and business rules somewhere there's no way round them, right up close with the data. Maybe T-SQL or C# isn't the prettiest language going - but SPs and triggers are the right tool for the job. 

Other than that, managing code deployed in the database is actually no different to managing code deployed in an application server (e.g. J2EE, COM objects, whatever). It's a matter of just not treating it as anything "special", having proper discipline about versioning, building and releasing it, etc. How is it "hidden" at your site?