备份数据库密码导出数据库密码 SQL Server SSMS

https://docs.microsoft.com/zh-CN/troubleshoot/sql/security/transfer-logins-passwords-between-instances

在登录名和密码的实例之间SQL Server

项目
2022/01/26

本文介绍如何在 SQL Server 上运行的不同实例之间传输登录名Windows。

原始产品版本： SQL Server
原始 KB 编号： 918992

简介

本文介绍如何在不同登录实例之间传输登录名和密码Microsoft SQL Server。

备注

实例可能在同一台服务器上或在不同的服务器上，并且其版本可能不同。

详细信息

本文中，服务器 A 和服务器 B 是不同的服务器。

将数据库从服务器 A 上的 SQL Server 实例移动到服务器 B 上的 SQL Server 实例后，用户可能无法登录到服务器 B 上的数据库。此外，用户可能会收到以下错误消息：

用户 'MyUser'登录失败。 (Microsoft SQL Server，错误：18456)

出现此问题的原因是您未将登录名和密码从服务器 A 上的 SQL Server 实例转移到服务器 B 上的 SQL Server 实例。

备注

由于其他原因，也会出现 18456 错误消息。有关这些原因和潜在解决方案的其他信息，请参阅MSSQLSERVER_18456。

若要传输登录名，请根据情况使用以下方法之一。

方法 1：在服务器 B 上SQL Server目标 (密码)

若要解决此问题，请重置计算机SQL Server密码，然后脚本退出登录。

备注

重置密码时，会使用密码哈希算法。
方法 2： (使用源服务器和服务器 A) 上生成的脚本) 将登录名和密码 (服务器 B)

创建有助于生成必要的脚本以传输登录名及其密码的存储过程。为此，请通过使用 SQL Server Management Studio (SSMS) 任何其他客户端工具连接到服务器 A 并运行以下脚本：

SQL

  1  USE [master]
  2   GO
  3   IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  4   DROP PROCEDURE sp_hexadecimal
  5   GO
  6   CREATE PROCEDURE [dbo].[sp_hexadecimal]
  7   (
  8       @binvalue varbinary(256),
  9       @hexvalue varchar (514) OUTPUT
 10   )
 11   AS
 12   BEGIN
 13       DECLARE @charvalue varchar (514)
 14       DECLARE @i int
 15       DECLARE @length int
 16       DECLARE @hexstring char(16)
 17       SELECT @charvalue = '0x'
 18       SELECT @i = 1
 19       SELECT @length = DATALENGTH (@binvalue)
 20       SELECT @hexstring = '0123456789ABCDEF'
 21 
 22       WHILE (@i <= @length)
 23       BEGIN
 24             DECLARE @tempint int
 25             DECLARE @firstint int
 26             DECLARE @secondint int
 27 
 28             SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 29             SELECT @firstint = FLOOR(@tempint/16)
 30             SELECT @secondint = @tempint - (@firstint*16)
 31             SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
 32 
 33             SELECT @i = @i + 1
 34       END 
 35       SELECT @hexvalue = @charvalue
 36   END
 37   go
 38   IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
 39   DROP PROCEDURE sp_help_revlogin
 40   GO
 41   CREATE PROCEDURE [dbo].[sp_help_revlogin]   
 42   (
 43       @login_name sysname = NULL 
 44   )
 45   AS
 46   BEGIN
 47       DECLARE @name                     SYSNAME
 48       DECLARE @type                     VARCHAR (1)
 49       DECLARE @hasaccess                INT
 50       DECLARE @denylogin                INT
 51       DECLARE @is_disabled              INT
 52       DECLARE @PWD_varbinary            VARBINARY (256)
 53       DECLARE @PWD_string               VARCHAR (514)
 54       DECLARE @SID_varbinary            VARBINARY (85)
 55       DECLARE @SID_string               VARCHAR (514)
 56       DECLARE @tmpstr                   VARCHAR (1024)
 57       DECLARE @is_policy_checked        VARCHAR (3)
 58       DECLARE @is_expiration_checked    VARCHAR (3)
 59       Declare @Prefix                   VARCHAR(255)
 60       DECLARE @defaultdb                SYSNAME
 61       DECLARE @defaultlanguage          SYSNAME     
 62       DECLARE @tmpstrRole               VARCHAR (1024)
 63 
 64   IF (@login_name IS NULL)
 65   BEGIN
 66       DECLARE login_curs CURSOR 
 67       FOR 
 68           SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
 69           FROM  sys.server_principals p 
 70           LEFT JOIN sys.syslogins     l ON ( l.name = p.name ) 
 71           WHERE p.type IN ( 'S', 'G', 'U' ) 
 72             AND p.name <> 'sa'
 73           ORDER BY p.name
 74   END
 75   ELSE
 76           DECLARE login_curs CURSOR 
 77           FOR 
 78               SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
 79               FROM  sys.server_principals p 
 80               LEFT JOIN sys.syslogins        l ON ( l.name = p.name ) 
 81               WHERE p.type IN ( 'S', 'G', 'U' ) 
 82                 AND p.name = @login_name
 83               ORDER BY p.name
 84 
 85           OPEN login_curs 
 86           FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
 87           IF (@@fetch_status = -1)
 88           BEGIN
 89                 PRINT 'No login(s) found.'
 90                 CLOSE login_curs
 91                 DEALLOCATE login_curs
 92                 RETURN -1
 93           END
 94 
 95           SET @tmpstr = '/* sp_help_revlogin script '
 96           PRINT @tmpstr
 97 
 98           SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
 99 
100           PRINT @tmpstr
101           PRINT ''
102 
103           WHILE (@@fetch_status <> -1)
104           BEGIN
105             IF (@@fetch_status <> -2)
106             BEGIN
107                   PRINT ''
108 
109                   SET @tmpstr = '-- Login: ' + @name
110 
111                   PRINT @tmpstr
112 
113                   SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
114                   BEGIN'
115                   Print @tmpstr 
116 
117                   IF (@type IN ( 'G', 'U'))
118                   BEGIN -- NT authenticated account/group 
119                     SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
120                   END
121                   ELSE 
122                   BEGIN -- SQL Server authentication
123                           -- obtain password and sid
124                           SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
125 
126                           EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
127                           EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
128 
129                           -- obtain password policy state
130                           SELECT @is_policy_checked     = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
131                           FROM sys.sql_logins 
132                           WHERE name = @name
133 
134                           SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
135                           FROM sys.sql_logins 
136                           WHERE name = @name
137 
138                           SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' 
139                                           + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
140 
141                           IF ( @is_policy_checked IS NOT NULL )
142                           BEGIN
143                             SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
144                           END
145 
146                           IF ( @is_expiration_checked IS NOT NULL )
147                           BEGIN
148                             SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
149                           END
150           END
151 
152           IF (@denylogin = 1)
153           BEGIN -- login is denied access
154               SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
155           END
156           ELSE IF (@hasaccess = 0)
157           BEGIN -- login exists but does not have access
158               SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
159           END
160           IF (@is_disabled = 1)
161           BEGIN -- login is disabled
162               SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
163           END 
164 
165           SET @Prefix = '
166           EXEC master.dbo.sp_addsrvrolemember @loginame='''
167 
168           SET @tmpstrRole=''
169 
170           SELECT @tmpstrRole = @tmpstrRole
171               + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
172               + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
173               + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
174               + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
175               + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
176               + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
177               + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
178               + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
179             FROM (
180                       SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
181                               sysadmin,
182                               securityadmin,
183                               serveradmin,
184                               setupadmin,
185                               processadmin,
186                               diskadmin,
187                               dbcreator,
188                               bulkadmin
189                       FROM sys.syslogins
190                       WHERE (       sysadmin<>0
191                               OR    securityadmin<>0
192                               OR    serveradmin<>0
193                               OR    setupadmin <>0
194                               OR    processadmin <>0
195                               OR    diskadmin<>0
196                               OR    dbcreator<>0
197                               OR    bulkadmin<>0
198                           ) 
199                           AND name=@name 
200                 ) L 
201 
202               PRINT @tmpstr
203               PRINT @tmpstrRole
204               PRINT 'END'
205           END 
206           FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
207       END
208       CLOSE login_curs
209       DEALLOCATE login_curs
210       RETURN 0
211   END

备注

此脚本在主数据库中创建两个存储过程。这些过程名为 sp_hexadecimal 和 sp_help_revlogin 。
在SSMS编辑器中，选择"结果到文本" 选项。
在同一个或新的查询窗口中运行以下语句：

SQL
```
EXEC sp_help_revlogin
```
存储过程生成的 sp_help_revlogin 输出脚本是登录脚本。此登录脚本创建具有原始安全标识符的登录名 (SID) 和原始密码。

重要

在目标服务器上继续实现步骤之前，请查看下面的"备注"部分的信息。

目标服务器 B 上 (的步骤)

连接客户端工具（如 (）SSMS) 服务器 B，然后运行步骤 4 中生成的脚本 (服务器 A) sp_helprevlogin 输出。

备注

在服务器 B 上对实例运行输出脚本之前，请查看以下信息：

可以通过以下方法对密码进行哈希处理：
- VERSION_SHA1：此哈希使用 SHA1 算法生成，在 SQL Server 2000 到 SQL Server 2008 R2 中使用。
- VERSION_SHA2：此哈希使用 SHA2 512 算法生成，在 SQL Server 2012 及更高版本中使用。
仔细阅读输出脚本。如果服务器 A 和服务器 B 在不同的域中，您必须更改输出脚本。然后，您必须使用 CREATE LOGIN 语句中的新域名替换原始域名。新域中授予访问权限的集成登录名的 SID 与原始域中的登录名不同。因此，用户会从这些登录名中孤立。若要详细了解如何解决这些孤立用户，请参阅如何在运行数据库的服务器之间移动数据库时解决SQL Server。

如果服务器 A 和服务器 B 位于同一域中，则使用相同的 SID。因此，用户不太可能成为孤立用户。
在输出脚本中，登录名是使用加密密码创建的。这是因为语句中的 HASHED CREATE LOGIN 参数。此参数指定 PASSWORD 参数之后输入的密码已经过哈希处理。
默认情况下，只有 sysadmin 固定服务器角色的成员才能从视图中 SELECT 运行 sys.server_principals 语句。除非 sysadmin 固定服务器角色的成员向用户授予必要的权限，否则用户无法创建或运行输出脚本。
本文中的步骤不会传输特定登录的默认数据库信息。这是因为默认数据库可能并不总是存在于服务器 B 上。若要定义登录的默认数据库，请使用语句，将登录名和默认 ALTER LOGIN 数据库作为参数传递。
对源和目标服务器中的订单进行排序：
- 不区分大小写的服务器 A 和区分大小写的服务器 B：服务器 A 的排序顺序不区分大小写，服务器 B 的排序顺序可能区分大小写。在这种情况下，在将登录名和密码转移到服务器 B 上的实例后，用户必须键入所有大写字母的密码。
- 区分大小写的服务器 A 和不区分大小写的服务器 B：服务器 A 的排序顺序可能区分大小写，并且服务器 B 的排序顺序不区分大小写。在这种情况下，用户无法使用您转移到服务器 B 上实例的登录名和密码登录，除非满足下列条件之一：
  - 原始密码不包含字母。
  - 原始密码的所有字母都是大写字母。
- 在两台服务器上区分大小写：服务器 A 和服务器 B 的排序顺序可能区分大小写，或者服务器 A 和服务器 B 的排序顺序不区分大小写。在这些情况下，用户不会遇到问题。
服务器 B 实例中已有的登录名的名称与输出脚本中的名称相同。在这种情况下，当您在服务器 B 上的实例运行输出脚本时，会收到以下错误消息：

Msg 15025，级别 16，状态 1，第 1 行
服务器主体 "MyLogin" 已存在。

同样，已位于服务器 B 实例中的登录名的 SID 可能与输出脚本中的 SID 相同。在这种情况下，当您在服务器 B 上的实例运行输出脚本时，会收到以下错误消息：

Msg 15433，级别 16，状态 1，第 1 行提供的参数 sid 被使用。

因此，您必须执行以下操作：
1. 仔细阅读输出脚本。
2. 检查服务器 B sys.server_principals实例中数据库视图的内容。
3. 适当地处理这些错误消息。
  
  在 SQL Server 2005 中，登录的 SID 用于实现数据库级访问。登录名在服务器上的不同数据库中可能具有不同的 ID。在这种情况下，登录名只能访问 SID 与视图中的 SID 匹配的 sys.server_principals 数据库。如果两个数据库从不同的服务器组合在一起，则可能会出现此问题。若要解决此问题，请通过使用 DROP USER 语句手动从 SID 不匹配的数据库中删除登录名。然后，使用语句再次添加 CREATE USER 登录名。