CODEWITHSUNDEEP
azure-sql-databasedacpacsqlpackage
Vignesh Ramesh
Vignesh Ramesh

Reputation: 5

How to include Sql Permission Statement with the generated DACPAC file

When I try to create a DACPAC file for my source database the SQL user permissions present in the source database is not getting included. Whenever I deploy the source database DACPAC file to target database the entire schema is getting created properly on my target database except the user permissions present in the source database. I have tried checking my source database DACPAC file by unpacking it and the user permissions are not included in the model file.

The script I am using to generate a DACPAC file from my source database is below :

sqlpackage /a:extract /of:true /ssn:tcp:test.database.windows.net,1433 /sdn:source_db /su:admin /sp:password /tf:"C:\SQL Server Management Studio\DAC Packages\source.dacpac";

The script I am using to deploy the source database DACPAC file to target database is below :

sqlpackage /a:publish /sf:"C:\SQL Server Management Studio\DAC Packages\source.dacpac" /tsn:tcp:test.windows.net,1433 /tdn:target_db /tu:admin /tp:password

Should I include some property in this ?

Upvotes: 0

Views: 1153

Answers (3)

user17493998
user17493998

Reputation: 1

sqlpackage /a:extract  /p:IgnorePermissions=false

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

DACPAC does not have security objects as part of the DACPAC. We need to include the permissions as part of the post deployment script. I am giving example security activities as part of the post deployment script.

  1. Create Logins
  2. Create Users
  3. Create Roles
  4. Permissions to Roles, Users ...

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16401

Just for now, the data migration with DACPAC file in Azure SQL database can't migrate the user and it's permission.

You need manually create the user or grant the user permission. There isn't a way can include the user permission.

Upvotes: 2

Related Questions