如何在 SQL Server 2005 和 SQL Server 2008 的執行個體之間傳送登入和密碼

文章翻譯 文章翻譯
文章編號: 918992 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

在此頁中

簡介

本文說明如何在 Microsoft SQL Server 2005、Microsoft SQL Server 2008 等不同伺服器上的執行個體之間傳送登入和密碼。

如需有關如何在其他版本 SQL Server 執行個體之間傳送登入和密碼的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
246133 如何在 SQL Server 的執行個體之間傳送登入和密碼

其他相關資訊

在本文中,伺服器 A 和伺服器 B 是不同的伺服器。此外,伺服器 A 和伺服器 B 都是執行 SQL Server 2005。

注意此資訊亦適用於 SQL Server 2008。

將資料庫從伺服器 A 上的 SQL Server 執行個體移到伺服器 B 上的 SQL Server 執行個體之後,使用者可能會無法登入伺服器 B 上的資料庫。此外,使用者可能會收到下列錯誤訊息:
使用者 'MyUser' 的登入失敗。(Microsoft SQL Server,錯誤:18456)
之所以發生這個問題,是因為您未將登入和密碼從伺服器 A 上的 SQL Server 執行個體傳送到伺服器 B 上的 SQL Server 執行個體。

如果要將登入和密碼從伺服器 A 上的 SQL Server 執行個體傳送到伺服器 B 上的 SQL Server 執行個體,請依照下列步驟執行:
  1. 在伺服器 A 上,啟動 SQL Server Management Studio,然後連線至先前從該處移動資料庫的 SQL Server 的執行個體。
  2. 開啟新的 [查詢編輯器] 視窗,然後執行下列指令碼。
    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
    
    注意 這個指令碼會在 master 資料庫中建立兩個預存程序。這兩個預存程序名為 sp_hexadecimal 預存程序和 sp_help_revlogin 預存程序。
  3. 執行下列陳述式。
    EXEC sp_help_revlogin
    sp_help_revlogin 預存程序所產生的輸出指令碼是登入指令碼。這個登入指令碼會建立具有原始「安全性識別碼」(SID) 和原始密碼的登入。
  4. 在伺服器 B 上,啟動 SQL Server Management Studio,然後連線至先前將資料庫移至該處的 SQL Server 的執行個體。

    重要 在進行步驟 5 之前,請先檢閱<備註>一節中的資訊。
  5. 開啟新的 [查詢編輯器] 視窗,然後執行在步驟 3 中產生的輸出指令碼。

備註

在伺服器 B 上的執行個體上執行輸出指令碼之前,請先檢閱下列資訊:
  • 仔細檢閱輸出指令碼。如果伺服器 A 和伺服器 B 是在不同的網域中,您必須修改輸出指令碼。然後,您必須在 CREATE LOGIN 陳述式中,使用新的網域名稱取代原始的網域名稱。在新網域中授與存取權的整合式登入沒有與原始網域中的登入相同的 SID。因此,使用者是孤立於這些登入之外。 如需有關如何解決這些孤立使用者的詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
    240872 INF:如何解決在 SQL Server 之間移動資料庫的權限問題
    如果伺服器 A 和伺服器 B 是在相同的網域中,就會使用相同的 SID。因此,使用者不可能是孤立的。
  • 在輸出指令碼中,登入是使用加密的密碼而建立的。這是因為 CREATE LOGIN 陳述式中的 HASHED 引數的緣故。這個引數會指定在 PASSWORD 引數之後輸入的密碼已經是雜湊密碼。
  • 根據預設,只有 sysadmin 固定伺服器角色的成員可以從 sys.server_principals 檢視執行 SELECT 陳述式。除非 sysadmin 固定伺服器角色的成員授與必要權限給使用者,否則使用者無法建立或執行輸出指令碼。
  • 本文中的步驟無法為特定登入傳送預設資料庫資訊。這是因為預設資料庫不一定永遠在伺服器 B 上。如果要定義登入的預設資料庫,請傳入登入名稱和預設資料庫做為引數,藉此使用 ALTER LOGIN 陳述式。
  • 伺服器 A 的排序順序可能不必區分大小寫,伺服器 B 的排序順序則可能要區分大小寫。在這種情況中,在您將登入和密碼傳送到伺服器 B 上的執行個體之後,使用者必須將密碼中的所有字母以大寫字母輸入。

    或者,伺服器 A 的排序順序可能要區分大小寫,伺服器 B 的排序順序則可能不必區分大小寫。在這種情況中,使用者無法使用您傳送到伺服器 B 上的執行個體的登入和密碼來登入,除非下列其中一種情況成立:
    • 原始密碼中沒有字母。
    • 原始密碼中的所有字母都是大寫字母。
    伺服器 A 和伺服器 B 的排序順序可能要區分大小寫,也可能不必區分大小寫。在上述情況中,使用者不會遇到問題。
  • 已存在於伺服器 B 上執行個體中的登入名稱可能會與輸出指令碼中的名稱相同。在這種情況中,當您在伺服器 B 上的執行個體上執行輸出指令碼時,會收到下列錯誤訊息:
    訊息 15025,層級 16,狀態 1,行 1
    伺服器主體 'MyLogin' 已經存在。
    同樣地,已存在於伺服器 B 上執行個體中的登入 SID 可能會與輸出指令碼中的 SID 相同。在這種情況中,當您在伺服器 B 上的執行個體上執行輸出指令碼時,會收到下列錯誤訊息:
    訊息 15433,層級 16,狀態 1,行 1
    提供的參數 sid 正在使用中。
    因此,您必須執行下列操作:
    1. 仔細檢閱輸出指令碼。
    2. 檢查伺服器 B 上執行個體中的 sys.server_principals 檢視的內容。
    3. 解決這些錯誤訊息。
  • 在 SQL Server 2005 中,登入的 SID 會用來做為實作資料庫層級存取的基礎。在伺服器上的兩個不同資料庫中,登入可能會有兩個不同的 SID。在這種情況中,登入只能存取其 SID 與 sys.server_principals 檢視中的 SID 相符的資料庫。如果兩個資料庫是從兩個不同的伺服器合併的,可能就會發生這個問題。如果要解決這個問題,請使用 DROP USER 陳述式從其 SID 不符的資料庫手動移除登入。然後,使用 CREATE USER 陳述式再次加入登入。

?考

如需有關如何疑難排解孤立使用者的詳細資訊,請造訪下列 Microsoft Developer Network (MSDN) 網站:
http://msdn.microsoft.com/zh-tw/library/ms175475.aspx
如需有關 CREATE LOGIN 陳述式的詳細資訊,請造訪下列 MSDN 網站:
http://msdn.microsoft.com/zh-tw/library/ms189751.aspx
如需有關 ALTER LOGIN 陳述式的詳細資訊,請造訪下列 MSDN 網站:
http://msdn.microsoft.com/zh-tw/library/ms189828.aspx

屬性

文章編號: 918992 - 上次校閱: 2013年7月16日 - 版次: 4.2
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
關鍵字:?
kbsqlsetup kbexpertiseadvanced kbhowto kbinfo KB918992
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com