在登录名和密码的实例之间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 被使用。
- 因此，您必须执行以下操作：
- 仔细阅读输出脚本。
- 检查服务器 B sys.server_principals实例中数据库视图的内容。
- 适当地处理这些错误消息。
在 SQL Server 2005 中，登录的 SID 用于实现数据库级访问。 登录名在服务器上的不同数据库中可能具有不同的 ID。 在这种情况下，登录名只能访问 SID 与视图中的 SID 匹配的sys.server_principals数据库。 如果两个数据库从不同的服务器组合在一起，则可能会出现此问题。 若要解决此问题，请通过使用 DROP USER 语句手动从 SID 不匹配的数据库中删除登录名。 然后，使用 语句再次添加CREATE USER登录名。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
