错误:SQL71564迁移到Azure时 [英] Error: SQL71564 When migrating to Azure

问题描述

我正在尝试第一次迁移到Azure，但出现以下错误:

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

在用作部分的架构中找到一个或多个不受支持的元素 数据包.错误SQL71564:验证元素[开发]时出错: 元素[develop]已从其登录名孤立出来，不能为 部署. (Microsoft.SqlServer.Dac)

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)

我有点困惑，因为"develop"是我们一直用于测试的用户名，仅此而已.

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

背景:

• 当前数据库正在SQL 2016 Express上运行.
• 我正在使用Microsoft SQL Server Management Studio 2016(为了确保最新版本已下载)
• 该架构是使用Entity Framework/从C#类进行的迁移创建的，因此我看不到任何异常.
• 所有表都具有主键
• 表之间没有依赖关系(全部由代码完成)
• 我在此数据库上运行了SQLAzureMW(5.15.6)，但没有发现错误(报告中的所有内容均为绿色).

推荐答案

这种孤立情况的常见问题是还原数据库时.如果使用默认策略还原数据库，并且没有执行restore命令以外的任何步骤，则还将还原在该还原的数据库中创建的所有数据库用户.由于这些数据库用户没有登录名或与SQL登录名的连接已断开，因此需要采取一些步骤来重新附加这些对象.

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.

脚本

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

在脚本中，我们将数据库用户映射到SQL登录名，并将默认密码设置为"密码".根据您在SQL Server上设置的密码策略，此密码可能无法使用.请根据您的服务器策略更改默认密码.

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.

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

这篇关于错误:SQL71564迁移到Azure时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！