本文介绍如何在Microsoft SQL Server的不同实例之间传输登录名和密码。
注意实例可能位于同一服务器上,也可能位于不同的服务器上,其版本可能不同。
有关如何在其他版本的SQL Server的实例之间传输登录名和密码的详细信息,请单击下面的文章编号,以查看Microsoft知识库中相应的文章:
注意实例可能位于同一服务器上,也可能位于不同的服务器上,其版本可能不同。
有关如何在其他版本的SQL Server的实例之间传输登录名和密码的详细信息,请单击下面的文章编号,以查看Microsoft知识库中相应的文章:
【1】迁移登录账户方法
在本文中,服务器A和服务器B是不同的服务器。
将数据库从服务器A上的SQL Server实例移动到服务器B上的SQL Server实例后,用户可能无法登录到服务器B上的数据库。此外,用户可能会收到以下错误消息:
将数据库从服务器A上的SQL Server实例移动到服务器B上的SQL Server实例后,用户可能无法登录到服务器B上的数据库。此外,用户可能会收到以下错误消息:
用户' MyUser ' 登录失败。(Microsoft SQL Server,错误:18456)
出现此问题的原因是您没有将登录名和密码从服务器A上的SQL Server实例
传输到服务器B上的SQL Server实例。要传输登录名,请根据您的具体情况使用下列方法之一。
方法1:重置目标SQL Server计算机上的密码(服务器B)
要解决此问题,请在SQL Server计算机中重置密码,然后编写登录脚本。
注意重置密码时使用密码哈希算法。
方法2:使用在源服务器(服务器B)上生成的脚本将登录名和密码传输到目标服务器(服务器A)
传输到服务器B上的SQL Server实例。要传输登录名,请根据您的具体情况使用下列方法之一。
方法1:重置目标SQL Server计算机上的密码(服务器B)
要解决此问题,请在SQL Server计算机中重置密码,然后编写登录脚本。
注意重置密码时使用密码哈希算法。
方法2:使用在源服务器(服务器B)上生成的脚本将登录名和密码传输到目标服务器(服务器A)
【2】使用SP获取登录脚本的T-SQL
要创建具有空密码的登录脚本,请按照下列步骤操作:
- 在服务器A上,启动SQL Server Management Studio,然后连接到从中移动数据库的SQL Server实例。
- 打开一个新的查询编辑器窗口,然后运行以下脚本。登录名
USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO exec sp_help_revlogin
权限
--查看登录名服务器角色 SELECT sp.name AS [login_name] ,CASE WHEN sp.[type]='S' THEN 'SQL 登录名' WHEN sp.[type]='U' THEN 'Windows 登录名' WHEN sp.[type]='G' THEN 'Windows 组' WHEN sp.[type]='R' THEN '服务器角色' WHEN sp.[type]='C' THEN '映射到证书的登录名' WHEN sp.[type]='K' THEN '映射到非对称密钥的登录名' END AS [principal_type] ,sp.is_disabled ,ISNULL(sp.default_database_name,'') as [default_database_name] ,ISNULL(rsp.name,'') AS [server_role] ,STUFF((SELECT ','+permission_name FROM sys.server_permissions spp where sp.principal_id=spp.grantee_principal_id for xml path('')),1,1,'') as [permissions] FROM sys.server_principals sp LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id where rsp.name is not null ORDER BY [principal_type],sp.principal_id --授权服务器角色 select N'EXEC sp_addsrvrolemember N''' +sp.name+ ''' ,N''' + rsp.name+''' ' FROM sys.server_principals sp LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id where rsp.name is not null -- 将登录名添加为某个服务器级角色的成员 EXEC sp_addsrvrolemember @loginame= 'kk' ,@rolename = 'sysadmin' master库中的用户名及权限可以用如下脚本进行迁移。 -- 授予【数据库角色成员身份】权限 SELECT 'exec sp_addrolemember N'''+g.name+''', N'''+u.name+'''' FROM sys.database_principals u inner join sys.database_role_members m on u.principal_id = m.member_principal_id inner join sys.database_principals g on g.principal_id = m.role_principal_id ORDER BY g.name,u.name -- 授予【安全对象】权限 SELECT N'grant '+B.permission_name collate chinese_prc_ci_ai_ws+N' on ['+A.name+N'] to ['+C.name+N']' FROM sys.sysobjects A(NOLOCK) INNER JOIN sys.database_permissions B(NOLOCK) ON A.id=B.major_id INNER JOIN sys.database_principals C(NOLOCK) ON B.grantee_principal_id=C.principal_id --WHERE C.name='kk' --A.name='objectName'
- 在相同或新的查询窗口中运行以下语句:
EXEC sp_help_revlogin
目标服务器上的步骤(服务器B):
- 在服务器B上,启动SQL Server Management Studio,然后连接到移动数据库的SQL Server实例。
重要信息在转到第2步之前,请查看下面“备注”部分中的信息。 - 打开一个新的查询编辑器窗口,然后运行在前一过程的步骤2中生成的输出脚本。
备注
在服务器B上的实例上运行输出脚本之前,请查看以下信息:
- 可以通过以下方式对密码进行哈希处理:
- VERSION_SHA1:此哈希是使用SHA1算法生成的,并在SQL Server 2000到SQL Server 2008 R2中使用。
- VERSION_SHA2:此哈希是使用SHA2 512算法生成的,用于SQL Server 2012及更高版本。
- 仔细查看输出脚本。如果服务器A和服务器B位于不同的域中,则必须更改输出脚本。然后,您必须使用CREATE LOGIN语句中的新域名替换原始域名。在新域中授予访问权限的集成登录名与原始域中的登录名不同。因此,用户从这些登录中孤立。有关如何解决这些孤立用户的更多信息,请单击下面的文章编号,以查看Microsoft知识库中相应的文章:
如果服务器A和服务器B位于同一域中,则使用相同的SID。因此,用户不太可能成为孤儿。 - 在输出脚本中,使用加密密码创建登录。这是因为CREATE LOGIN语句中的HASHED参数。此参数指定在PASSWORD参数之后输入的密码已经过哈希处理。
- 默认情况下,只有sysadmin固定服务器角色的成员才能从sys.server_principals视图运行SELECT语句。除非sysadmin固定服务器角色的成员向用户授予必要的权限,否则用户无法创建或运行输出脚本。
- 本文中的步骤不会传输特定登录的默认数据库信息。这是因为服务器B上的默认数据库可能并不总是存在。要为登录定义默认数据库,请使用ALTER LOGIN语句,将登录名和默认数据库作为参数传入。
- 对源服务器和目标服务器排序:
- 不区分大小写的服务器A和区分大小写的服务器B:服务器A的排序顺序可能不区分大小写,服务器B的排序顺序可能区分大小写。在这种情况下,用户必须在将登录名和密码传输到服务器B上的实例后,以全大写字母键入密码。
- 区分大小写的服务器A和不区分大小写的服务器B:服务器A的排序顺序可能区分大小写,并且服务器B的排序顺序可能不区分大小写。在这种情况下,除非满足下列条件之一,否则用户无法使用您在服务器B上传输到实例的登录名和密码登录:
- 原始密码不包含字母。
- 原始密码中的所有字母均为大写字母。
- 两个服务器上区分大小写或不区分大小写:服务器A和服务器B的排序顺序可能区分大小写,或者服务器A和服务器B的排序顺序可能不区分大小写。在这些情况下,用户不会遇到问题。
- 已经在服务器B上的实例中的登录名可以具有与输出脚本中的名称相同的名称。在这种情况下,当您在服务器B上的实例上运行输出脚本时,您会收到以下错误消息:
消息15025,级别16,状态1,行1
服务器主体“ MyLogin ”已存在。消息15433,级别16,状态1,行1
提供的参数sid正在使用中。- 仔细查看输出脚本。
- 检查服务器B上实例中sys.server_principals视图的内容。
- 适当地解决这些错误消息。
在SQL Server 2005中,登录的SID用于实现数据库级访问。登录可以在服务器上的不同数据库中具有不同的SID。在这种情况下,登录只能访问具有与sys.server_principals视图中的SID匹配的SID的数据库。如果两个数据库从不同的服务器组合,则可能会出现此问题。若要解决此问题,请使用DROP USER语句从具有SID不匹配的数据库中手动删除登录。然后,使用CREATE USER语句再次添加登录。
- 如果您尝试使用脚本化的SQL Server 2000之前的登录名创建新的SQL Server 2012登录,则会收到以下错误消息:
消息15021,级别16,状态2,行1
参数PASSWORD的值无效。指定有效的参数值。
注意您在SQL Server 2012中收到此错误,因为为CREATE LOGIN和ALTER LOGIN语句提供了16字节的密码哈希。
若要在运行SQL Server 2012的服务器上解决此问题,请创建一个密码为空的登录名。为此,请运行以下脚本:
创建具有空密码的登录名后,用户可以在下次登录尝试时更改密码。CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
方法3:使用SQL Server 2000之前的密码登录
注意仅当您将SQL Server 2000迁移到更新的受支持版本的SQL Server时,此方法才适用。
在这种情况下,请求用户使用SQL Server 2000之前的登录名登录到运行SQL Server的服务器。
注意当用户使用SQL Server 2000之前的密码登录时,密码哈希会自动更新。
【3】使用T-SQL获取登录用户的T-SQL(推荐)
SELECT 'CREATE LOGIN [' + p.name + '] ' + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows ' ELSE '' END + 'WITH ' + CASE WHEN p.type = 'S' THEN 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' + CASE WHEN l.is_expiration_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + 'check_policy = ' + CASE WHEN l.is_policy_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + CASE WHEN l.credential_id > 0 THEN 'credential = ' + c.name + ', ' ELSE '' END ELSE '' END + 'default_database = ' + p.default_database_name + CASE WHEN LEN(p.default_language_name) > 0 THEN ', default_language = ' + p.default_language_name ELSE '' END FROM sys.server_principals p LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id WHERE p.type IN ( 'S', 'U', 'G' ) --AND p.name NOT IN ( 'sa') AND p.name NOT LIKE '%##%' AND p.name NOT LIKE '%NT SERVICE%' AND p.name NOT LIKE '%NT AUTHORITY%'
参考
有关如何解决孤立用户问题的详细信息,请转到孤立用户 Microsoft Developer Network(MSDN)网站疑难解答。
有关CREATE LOGIN语句的详细信息,请转到CREATE LOGIN(Transact-SQL) MSDN网站。
有关ALTER LOGIN语句的详细信息,请转到ALTER LOGIN(Transact-SQL) MSDN网站。
有关CREATE LOGIN语句的详细信息,请转到CREATE LOGIN(Transact-SQL) MSDN网站。
有关ALTER LOGIN语句的详细信息,请转到ALTER LOGIN(Transact-SQL) MSDN网站。