Permission required for deploy a dacpac

Question

I am trying to deploy a dacpac on tenant using sqlpackage.exe. Currently I am giving SysAdmin or db_owner permission to the account which will deploy this and it works fine. But in production, if the target tenant database is of some other application, I might not get these permissions, so would like to know the minimal permission that is required for this.

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

From here: Upgrade a Data-tier Application

Permissions

A DAC can only be upgraded by members of the sysadmin or serveradmin fixed server roles, or by logins that are in the dbcreator fixed server role and have ALTER ANY LOGIN permissions. The login must be the owner of the existing database. The built-in SQL Server system administrator account named sa can also upgrade a DAC.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 09 '13 at 00:18

Mark

2,926
3
28
31

score 0 · Answer 2 · answered Mar 12 '20 at 14:02

0

To update an existing database, you may need to disable the "Deploy database properties" advanced option if deploying in Visual Studio or "/p:ScriptDatabaseOptions=False" when deploying using SQLPACKAGE.EXE.

answered Mar 12 '20 at 14:02

Metaphor

6,157
10
54
77

bbsimonbb · Answer 3 · 2022-05-16T13:52:55.690

It may be difficult to find someone who'll give you sysadmin or serveradmin on your production DB server. If this is the case, consider using SqlPackage to generate scripts, comparing your DACPAC to the production schema, then run the scripts (requiring only dbowner). These two lines of powershell will do it for you...

SqlPackage /Action:"Script" /SourceFile:"path_to_my.dacpac" /TargetConnectionString:"my connection string" /p:"BlockOnPossibleDataLoss=False" /OutputPath:"c:\someFolder\DBDiff.sql"
Invoke-Sqlcmd -ConnectionString "my connection string" -InputFile "c:\someFolder\DBDiff.sql" -OutputSqlErrors $true

Permission required for deploy a dacpac

3 Answers3

Linked