Setting database to trustworthy will let you deploy assemblies in unsafe mode

The title says it all, but let me expand on this a little bit.

I have an assembly that I wanted to emulate in SQL server. The first, obvious choice is to use safe mode, which results in the following message:

Msg 6212, Level 16, State 1, Line 2
CREATE ASSEMBLY failed because method ‘x’ on type ‘x.x’ in safe assembly ‘x’ is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Be quiet my racing heart. Sassy SQL Server be sassy. So evidently I’d go ahead and deploy it in unsafe mode, just so that we can run into another error message:

Msg 10327, Level 14, State 1, Line 2
CREATE ASSEMBLY for assembly ‘CTXCRMEncryption’ failed because assembly ‘CTXCRMEncryption’ is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

SQL Server is actually trying to help in her quite, sassy way here, so let’s give her what she wants.

ALTER DATABASE databasename SET TRUSTWORTHY ON;

And now we can emulate the assembly in unsafe mode. I wish I had known that sooner.
I wish I had known that

Leave a Reply

Your email address will not be published. Required fields are marked *