The default deployment script for SQL CLR objects in VS2010 seems to require one to set the target database to SET TRUSTWORTHY ON and tries to enable vardecimal storage at the database level.
On our landscape we prefer to SET TRUSTWORTHY OFF on our databases. Also, one cannot enable vardecimal storage on system databases. We have some TVFs that we deploy to master.
It would be nice to be able to have the script be properly generated instead of having to modify it with each build. For now, I have abandoned the Deploy feature and manually deploy my CLR objects.
How do I customize the autogenerated deployment script? Is there a way?
Best Answer
for the trustworthy setting you should go to your project and: Right click on the project - Properties - Go to tab Project Settings -> Catalog properties file - Edit -> in the middle there will be option for set trustworthy on/off.
If you don't need to change the database properties at all at deploy time, then go to the Deploy tab - Deploy configuration file -> uncheck the option 'Deploy database properties' - first option in the list. This should not apply the settings from the 'Catalog Properties file' to the database your deploying on.
As for Vardecimal storage option I remember I saw it somewhere in the deploy settings.. but no clue for the moment, sorry. If I find it I'll get back with solution. Hope this helps, at least a bit.