Brief introduction 0 "style=" box-sizing:border-box; Outline:none; Margin-right:auto; Margin-left:auto; max-width:1600px; width:761.391px; " > This article describes how to transfer logins and passwords between Microsoft SQL Server 2005 instances on different servers.
For more information about how to transfer logins and passwords between other versions of the SQL Server instance, click the following article number to view the article in the Microsoft Knowledge Base:
246133 How to transfer logins and passwords between instances of SQL Server
More information 0 "style=" box-sizing:border-box; Outline:none; Margin-right:auto; Margin-left:auto; max-width:1600px; width:761.391px; " > In this article, Server A and server B are different servers. In addition, both server A and server B are running SQL Server 2005.
After you move a database from a SQL Server instance on server A to a SQL Server instance on server B, users may not be able to log on to the database that is moved to Server B. Also, users may receive the following message: User " MyUser"Login failed. (Microsoft SQL Server, Error: 18456) This problem occurs because you did not transfer the login and password from the SQL Server instance on server A to the SQL Server instance on server B.
To transfer logins and passwords from a SQL Server instance on server A to an instance of SQL Server on server B, follow these steps:
- On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server that you want to move the database from.
- Open a New Query Editor window, and then run the following script.
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 to @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 If 1 Then ' OFF ' if 0 Then ' OFF ' ELSE NULL END from Sys.sql_logins WH ERE name = @name
SELECT @is_expiration_checked = Case is_expiration_checked If 1 then ' on ' while 0 Then ' OFF ' ELSE NULL END from sys.sql_l Ogins 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 has 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 to @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Note: This script creates two stored procedures in the "master" database. Two stored procedures are named "sp_hexadecimal" stored Procedures and "sp_help_revlogin" stored procedures, respectively.
exec sp_help_revlogin The output script generated by the "sp_help_revlogin" stored procedure is a logon script. This logon script creates a login with the original security identifier (SID) and the original password. - On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.
Important : Before you perform step 5, review the information in the "Remarks" section.
- Open a New Query Editor window, and then run the output script that you generated in step 3.
Note 0 "style=" box-sizing:border-box; Outline:none; Margin-right:auto; Margin-left:auto; max-width:1600px; width:761.391px; " > Before you run an output script on an instance of server B, check the following information:
- Double-check the output script. If server A and server B are in different domains, you must modify the output script. You must then replace the original domain name with the new domain name in the CREATE LOGIN statement. Integrated logins that grant access in the new domain will not have the same SID as the logins in the original domain. As a result, users are orphaned from these logins. For more information about how to resolve these orphaned users, click the following article number to view the article in the Microsoft Knowledge Base:
240872 How to troubleshoot permissions issues when you move a database between servers that are running SQL Server
If server A and server B are in the same domain, the same SID is used. Therefore, the user cannot be isolated.
- In the output script, create a login by using an encrypted password. This is because the HASHED parameter is used in the CREATE LOGIN statement. This parameter specifies that the password entered after the PASSWORD parameter has been hashed.
- By default, only members of the sysadmin fixed server role can run SELECT statements from the Sys.server_principals view. Users cannot create or run output scripts unless the members of the sysadmin fixed server role grant the required permissions to the user.
- The steps in this article do not transfer the default database information for a specific login. This is because the default database cannot always exist on server B. To define the default database for a login, use the ALTER login statement and pass in the login name and default database as parameters.
- Server A's sort order may be case insensitive, and server B's sort order may be case sensitive. In this case, when you transfer the login and password to the instance on server B, you must type all the letters in the password in uppercase letters.
Alternatively, server A's sort order may be case sensitive, and server B's sort order may be case insensitive. In this situation, you will not be able to log on using the login and password that is transferred to the instance on server B unless one of the following conditions is met:
- The original password does not contain letters.
- All letters in the original password are uppercase letters.
The sort order of server A and server B may be case-sensitive, or it may not be case-sensitive. In these cases, the user does not experience the problem.
- mylogin ' already exists. Similarly, a login that already resides in an instance on server B may have the same SID as a SID in the output script. In this case, when you run an output script against an instance on server B, you receive the following error message: Msg 15433, Level 16, State 1, line 1
- Check the output script carefully.
- Check the contents of the Sys.server_principals view in the instance on server B.
-
- In SQL Server 2005, the SID that is logged on is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases in the server. In this case, the login can only access a database that has a SID that matches the SID in the Sys.server_principals view. This problem may occur when you merge these two databases from two different servers. To resolve this issue, you can use the Drop USERT statement to manually remove the corresponding login from the database that has a mismatched SID. Then, add the login again by using the CREATE USER statement.
Reference 0 "style=" box-sizing:border-box; outline:none; margin-right:auto; margin-left:auto; max-width:1600px; w idth:761.391px; " > For more information about how to resolve orphaned user issues, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn2.microsoft.com/zh-cn/library/ Ms175475.aspx For more information about the CREATE LOGIN statement, visit the following MSDN Web site: http://msdn2.microsoft.com/zh-cn/library/ms189751.aspx about ALTER For more information about the LOGIN statement, visit the following MSDN website: http://msdn2.microsoft.com/zh-cn/library/ms189828.aspx
How to transfer logins and passwords between instances of SQL Server 2005
