Turning on TRUSTWORTHY with a dacpac

Question

I am creating a database with an SSDT database project and deploying as a dacpac. A .NET assembly is part of the project and during deployment installation fails with the following error message:

CREATE ASSEMBLY for assembly 'xyz' failed because assembly 'xyz' 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.

The dbo is 'sa'. I have gone into the project properties and checked "Trustworthy" under the Miscellaneous tab. However, I still get the error and when I run

select name, is_trustworthy_on from sys.databases

I see that is_trustworthy_on is 0. It seems this setting has no effect. After searching, I found the following:

Some database options, such as TRUSTWORTHY, DB_CHAINING, and HONOR_BROKER_PRIORITY, cannot be adjusted as part of the deployment process.

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

The word adjusted implies changed to me. This is a new database. However, if it is true even for new databases, why does the setting exist in the dialog? Do I need to script this as part of the pre-deployment?

Answer 1

Since you already have the "Trustworthy" check-box in the "Miscellaneous" tab checked, you should only need to ensure that you have the option for "Deploy database properties" checked (or set to true). This option will be in different places, depending on exactly how you are publishing. Some places include:

• Going to the "Build" menu and selecting "Publish {project name}..."
  • "Publish Database" wizard appears.
  • Click on the "Advanced..." button
  • Top check-box is for "Deploy database properties"

• Using SqlPackage.exe:
  • Passing in via a profile file: <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
  • Passing in via command-line: /p:ScriptDatabaseOptions = True

• Using MSDeploy with the dbSqlPackage provider:
  • Option is: ScriptDatabaseOptions=True

Answer 2

Do you get a message to say you must be a sysadmin?? The user who does the deployment needs to be in the sysadmin group. There are some settings that are wrapped in:

IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
                SET TRUSTWORTHY ON 
                WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END