CODEWITHSUNDEEP
sqldeployment
Naaruto Uchiha
Naaruto Uchiha

Reputation: 83

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.

Upvotes: 7

Views: 10550

Answers (5)

Marco Merola
Marco Merola

Reputation: 879

Required Permissions for SQL Server Data Tools

https://learn.microsoft.com/en-us/sql/ssdt/required-permissions-for-sql-server-data-tools?view=sql-server-ver16#DatabaseCreationAndDeploymentPermissions

Deploy updates to an existing database

If you publish the dacpac with the following options:

/p:ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions;DatabaseOptions;Filegroups;Files"
/p:ScriptDatabaseOptions=False

then you just need a database user with:

ALTER ROLE db_ddladmin ADD MEMBER MyDbUser;    

ALTER ROLE db_datareader ADD MEMBER MyDbUser;    

ALTER ROLE db_datawriter ADD MEMBER MyDbUser;    

GRANT VIEW DEFINITION TO MyDbUser;

Otherwise you need the db user to be a db_owner.

Upvotes: 0

Nilesh Patel
Nilesh Patel

Reputation: 35

I encountered the same issue. In my case, the pipeline succeeded only by providing the db_owner role on the Production database.

Upvotes: 0

bbsimonbb
bbsimonbb

Reputation: 29020

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

Upvotes: 0

Metaphor
Metaphor

Reputation: 6415

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.

Upvotes: 0

Mark
Mark

Reputation: 2926

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.

Upvotes: 2

Related Questions