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?

I know it doesn't really answer your question, but you could try signing the assembly instead of making the database trustworthy. — Mansfield, Dec 01 '14 at 20:56
It's possible, but less than ideal. I'm under contract to automate the as-is state. — Swoogan, Dec 01 '14 at 21:11

Solomon Rutzky · Accepted Answer · 2014-12-01T22:49:46.770

3

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

edited Dec 01 '14 at 22:49

answered Dec 01 '14 at 22:34

Solomon Rutzky

46,688
9
128
171

1

Way to leave a guy is suspense! ;) – Swoogan Dec 01 '14 at 22:37
@Swoogan Sorry, had to check a few places first ;) – Solomon Rutzky Dec 01 '14 at 22:44
1

Thank you very much. Cargo cult programming. I copied another deployment script that had /p:ScriptDatabaseOptions=False in it. I clearly didn't know what it did. – Swoogan Dec 01 '14 at 22:50

score 0 · Answer 2 · edited Feb 06 '20 at 22:24

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

The login used for the deployment is part of the sysadmin role. However, when I generate the script and search for trustworthy, I don't get any results. How did you get it to generate that? — Swoogan, Dec 01 '14 at 22:23

Turning on TRUSTWORTHY with a dacpac

2 Answers2