Clever Geek Handbook

Enabling TRUSTWORTHY with dacpac

I am creating a database with SSDT database project and deploy as dacpac. The .NET assembly is part of the project and the following error message is not issued during setup installation:

CREATE ASSEMBLY for assembly "xyz" failed because assembly "xyz" is not allowed for PERMISSION_SET = UNSAFE. An assembly is allowed if one of the following conditions is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property. or the assembly is signed with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

RBS is "sa". I went into the project properties and checked "Reliable" under the "Miscellaneous" tab. However, I still get the error and when I run

select name, is_trustworthy_on from sys.databases

I see it is_trustworthy_on

is 0. This setting seems to have no effect. After searching, I found the following:

Some database parameters, 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 change. This is a new database. However, if this is true even for new databases, why does this option exist in the dialog box? Do I need to script this as part of the pre-deployment?

+3


source to share


2 answers


Since you already have the "Reliable" checkbox checked in the "Miscellaneous" tab, you only need to make sure you have the "Deploy database properties" option (or set to true). This setting will appear in different places depending on how you post. In some places:

  • Going to the Build menu and choosing Publish {project name} ...
    • The Publish Database wizard appears.
    • Click the "Advanced ..."
    • Top checkbox for "Deploy Database Properties"




  • Using SqlPackage.exe :
    • Going through the profile file: <ScriptDatabaseOptions>True</ScriptDatabaseOptions>

    • Going through the command line: /p:ScriptDatabaseOptions = True




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

+3


source


Do you get a message that you must be a system administrator? The user who is deploying must be in the sysadmin group. There are some settings that are wrapped in:



IF IS_SRVROLEMEMBER (N'sysadmin ') = 1 START IF EXISTS (SELECT 1 FROM [master]. [Dbo]. [Sysdatabases] WHERE [name] = N' $ (DatabaseName) ') START EXECUTE sp_executesql N'ALTER DATABASE [$ (DatabaseName)] SET A TRUSTED INCLUSION WITH ROLLBACK IMMEDIATE '; END END ELSE START PRINT N. Database parameters cannot be changed. You must be a SysAdmin to apply these parameters. '; END

0


source






More articles:


All Articles