Need Minimal Permissions for DacPack Deployment in VS 2019

Question

Hello everyone,

I'm facing a situation where I need to transition from having dbOwner permissions to a more restricted set of permissions in order to publish a DacPack for my Database project using Visual Studio 2019.

Currently, my user, 'newlogin,' has been granted the following permissions: ddlAdmin, dbReader, and dbWriter.

However, when attempting to publish with the permissions mentioned above, I encounter the following errors:

1.An error occurs while attempting to alter the database.

2.There is an issue with creating a schema with dbo authorization.

I'm seeking guidance on what would be the minimal set of permissions required for a successful deployment of the DacPack. Your insights and suggestions would be greatly appreciated.

Answer 1

To run ALTER DATABASE, you need ALTER permission on the database. That is

GRANT ALTER TO whomeveritmightbe

Answer 2

Hi @Ahwan Mishra To publish a dacpac and modify the database schema, the user needs DDL write permission to edit the database. If the user is publishing to a non-existent database, they must have permission to create a new database, otherwise, an error will occur.

Are you able to create a db in that server?

Regards

Geetha

Answer 3

Hi All,
I could resolve the above issues, the solution is as follow.

Q1. An error occurs while attempting to alter the database.

Ans- GRANT ALTER ON <Database>

Q2. There is an issue with creating a schema with dbo authorization.

Ans - assigned user an additional role of db_accessadmin