Error: SQL71564 When migrating to Azure

Question

I'm trying to do my first migration to Azure and I'm getting the following error:

One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: Error validating element [develop]: The element [develop] has been orphaned from its login and cannot be deployed. (Microsoft.SqlServer.Dac)

I'm a little confused, since 'develop' is the user name we have been using for testing, nothing more, nothing less.

Background:

• The current DB is running on SQL 2016 Express.
• I'm using Microsoft SQL Server Management Studio 2016 (just downloaded to be sure of latest version) for the migration
• The schema was created using Entity Framework / migrations from C# classes so nothing out of the ordinary that I can see.
• All tables have primary key
• There are no dependencies between tables (it's all done in code)
• I ran SQLAzureMW (5.15.6) on this DB and it found no errors (everything was green on the report).

Answer 1

A common problem with this orphaned situation is when a database is restored. If a database is restored using a default strategy and no steps other than the restore command are taken, all the database users that were created in that restored database will also be restored. Since these database users did not have a login or the connection to the SQL login has been severed, there are steps that need to be taken to reattach these objects.

Script

SET NOCOUNT ON
USE {your-db-name}
GO
DECLARE @loop INT
DECLARE @USER sysname
DECLARE @sqlcmd NVARCHAR(500) = ''
 
IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL 
 BEGIN
  DROP TABLE #orphaned
 END
 
CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1))
 
INSERT INTO #Orphaned (UserName)
SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')
 
IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS'
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password'''
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
     END
     
    SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']'
    Exec(@sqlcmd)
    PRINT @USER + ' link to DB user reset';
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF

In the script we are mapping database users to SQL login and setting up default password as 'password' This might not work depending on the password policy set on your SQL Server. Please change the default password as per your Server Policy.

Reference

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users/