错误: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] 时出错:元素 [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)

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

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(刚刚下载以确保最新版本)进行迁移
• 架构是使用实体框架/从 C# 类迁移创建的，因此我看不到任何异常.
• 所有表都有主键
• 表之间不存在依赖关系(全部在代码中完成)
• 我在这个数据库上运行了 SQLAzureMW (5.15.6)，它没有发现任何错误(报告中的一切都是绿色的).

推荐答案

这种孤立情况的一个常见问题是在恢复数据库时.如果使用默认策略恢复数据库并且除了恢复命令之外没有执行任何步骤，那么在该恢复的数据库中创建的所有数据库用户也将被恢复.由于这些数据库用户没有登录或与 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 登录名并将默认密码设置为password"，这取决于您的 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屋！