'Permission required for deploy a dacpac
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.
Solution 1:[1]
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.
Solution 2:[2]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Metaphor |
| Solution 2 |