sqlpackage.exe: any way to ignore users on export?

Question

I'm trying to create a bacpac file to export my databases to azure.
Is there anyway of making it ignore the users, while creating an export package (sqlpackage /a:Export) ?

Apatite

Answer 1

No, unfortunately, there is no option to ignore users on export.

You could, in the alternative, produce a dacpac file with data (sqlpackage /a:extract /p:ExtractAllTableData=true) and ignore users when publishing... but that would only work for a pristine database, because dacpacs skip most Azure import niceties (like publishing stored procedures first to take advantage of deferred name resolution).

I'm guessing that you would like to ignore users because your database contains some users mapped to Windows logins and you'd like to avoid producing a new version of the database that contains only Azure SQL DB-compatible objects. If so, you might be interested in trying the private preview of the Azure SQL Database migration service: https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/25/get-to-cloud-faster-with-a-new-database-migration-service-private-preview/

Gravitative

Yes, I've got a couple of dbs which have windows logins and orphan users. To solve this problem, I've added a couple of steps before creating the bacpac. I start by backing up the db, then I restore it with a different name and perform user clean up. After cleaning up the users, I go ahead and perform the bacpac creation and import it to azure. It's working, but now, I've got a couple of dbs which use linked servers...These will have to be dealt on a case by case analysis.. – Apatite

Sample command using Extract action would be: c:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe /TargetFile:"c:\tmpdir\file.dacpac" /Action:Extract /SourceServerName:"srcserver" /SourceDatabaseName:"srcdb" /p:ExtractAllTableData=true – Lucubrate

Answer 2

This functionality is intended to support the process of migrating databases to Azure. It therefore the reasoning goes that it only supports Database Artefacts that are supported by Azure. Windows Users, File groups and some hints (nolock without a WITH springs to mind) are not allowed.

One workflow that worked for me can be outlined as follows.

Export a Database Application Project of your database (dacpac file).

Import the same into Visual Studio (Version 2013 at least).

Change the Target Platform to Azure SQL Database (I am unsure about the V12 appendage)

Fix all the errors and warnings.

Optionally consider scripting to delete any data that could be removed at this point. You may also want to produce a quite small version for iterative testing.

Restore a backup (from a good old fashioned BAK file) of your Database to a different location.

Publish the dacpac of your Azure-corrected Project onto the restored DB.

Generate a Bacpac from the same.

Once you have a bacpac file, you should be able to promote it to the cloud

Obviously this will be an iterative learning process and some of the steps will require a number of tries to get right.

Sanctitude