Cara mentransfer login dan password antara contoh dari SQL Server

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 918992 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

Pada Halaman ini

PENGENALAN

Artikel ini menjelaskan cara mentransfer login dan password antara contoh Microsoft SQL Server 2005, Microsoft SQL Server 2008 dan Microsoft SQL Server 2012 pada server yang berbeda.

Untuk informasi lebih lanjut tentang bagaimana untuk mentransfer login dan password antara contoh dari versi lain dari SQL Server, klik nomor artikel berikut ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
246133 Cara mentransfer login dan password antara contoh dari SQL Server

Informasi lebih lanjut

Dalam artikel ini, server A dan server B adalah server yang berbeda. Selain itu, server A dan server B menjalankan SQL Server 2005.

Catatan Informasi ini juga berlaku untuk SQL Server 2008 dan SQL Server 2012.

Setelah Anda memindahkan database dari contoh SQL Server pada server A ke contoh SQL Server pada server B, pengguna mungkin tidak dapat masuk ke database pada server B. Selain itu, pengguna dapat menerima pesan galat berikut:
Login gagal untuk pengguna 'MyUser'. (Microsoft SQL Server, Error: 18456)
Masalah ini terjadi karena Anda lakukan tidak mentransfer login dan password dari contoh SQL Server pada server A ke contoh SQL Server pada server B.

Untuk mentransfer login, gunakan salah satu metode berikut, yang sesuai untuk situasi Anda.

Metode 1: Login dengan menggunakan password yang pra-SQL Server 2000

Untuk mengatasi masalah ini, minta pengguna untuk masuk ke server yang menjalankan SQL Server dengan menggunakan pra-SQL Server 2000 login.

Catatan Password hashing adalah diperbarui secara otomatis ketika pengguna log in dengan menggunakan pra-SQL Server 2000 password.

Metode 2: Membuat ulang sandi di SQL Server

Untuk mengatasi masalah ini, membuat ulang sandi di SQL Server, dan kemudian script keluar login.

Catatan Password hashing algoritma digunakan ketika membuat ulang sandi.

Metode 3: Membuat log dalam script yang memiliki password kosong

Untuk membuat log dalam script yang memiliki password kosong, ikuti langkah berikut:
  1. Pada server A, mulai SQL Server Management Studio, dan kemudian terhubung ke contoh SQL Server yang Anda pindah database.
  2. Buka jendela Query Editor yang baru, dan kemudian jalankan script berikut.
    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
    


    CatatanScript ini menciptakan dua prosedur tersimpan dalam masterdatabase. Prosedur yang bernamasp_hexadecimal dan sp_help_revlogin.
  3. Menjalankan pernyataan berikut:
    EXEC sp_help_revlogin
    Script output yang menghasilkan sp_help_revlogin disimpan prosedur adalah login script. Script login ini menciptakan login yang memiliki pengidentifikasi keamanan asli (SID) dan sandi asli.
  4. Pada server B, mulai SQL Server Management Studio, dan kemudian terhubung ke contoh SQL Server yang Anda pindah database.

    Penting Sebelum Anda pergi ke langkah 5, meninjau informasi di bagian "Komentar".
  5. Buka jendela Query Editor baru, dan kemudian jalankan script output yang dihasilkan pada langkah 3.

Komentar

Meninjau informasi berikut sebelum Anda menjalankan output script pada contoh pada server B:
  • Jika Anda mencoba untuk membuat login SQL Server 2012 baru dengan menggunakan pra-SQL Server 2000 login yang scripted, Anda menerima galat berikut:
    MSG 15021, tingkat 16, negara bagian 2, garis jatuh berseri 1
    Nilai yang valid diberikan untuk parameter PASSWORD. Menetapkan nilai parameter yang berlaku.
    Catatan Anda menerima galat ini dalam SQL Server 2012 karena 16-byte password hash yang disediakan untuk menciptakan LOGIN dan mengubah LOGIN pernyataan.

    Untuk mengatasi masalah ini pada server yang menjalankan SQL Server 2012, membuat login yang memiliki sandi kosong. Untuk melakukan ini, Jalankan script berikut:
    CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, 
    DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

    Setelah Anda membuat login yang memiliki sandi kosong, pengguna dapat mengubah sandi pada upaya login berikutnya.
  • Sandi dapat hashed dalam tiga cara:
    • VERSION_LEGACY: hash ini adalah 16-byte pra-SQL Server 2000 hash.
    • VERSION_SHA1: yang dihasilkan dengan menggunakan algoritma SHA1 hash ini dan digunakan dalam SQL Server 2000 melalui SQL Server 2008 R2.
    • VERSION_SHA2: hash ini dihasilkan dengan menggunakan algoritma SHA2 512 dan digunakan dalam SQL Server 2012.
  • Dalam SQL Server 2008 R1 dan versi sebelumnya, pra-SQL Server 2000 password hash didukung. Ketika seorang pengguna log in dengan menggunakan password yang digunakan pra-SQL Server 2000 hash, hash ditingkatkan untuk menggunakan password SHA1 hash.
  • Jika pengguna yang memiliki password yang menggunakan pra-SQL Server 2000 hash yang ada di server yang menjalankan SQL Server 2008 R2, ini berarti bahwa pengguna telah tidak masuk ke server.
  • Meninjau output script dengan hati-hati. Jika server A dan server B di domain yang berbeda, Anda harus mengubah output script. Kemudian, Anda harus mengganti nama domain asli dengan menggunakan nama domain baru dalam pernyataan membuat LOGIN. Login terpadu yang akan diberikan akses dalam domain baru tidak memiliki SID sama sebagai login di domain asli. Oleh karena itu, pengguna yang yatim piatu dari login ini. Untuk informasi lebih lanjut tentang cara menetapkan pengguna yatim ini, klik nomor artikel berikut ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
    240872 Cara mengatasi masalah izin ketika Anda memindahkan basis data antara server yang menjalankan SQL Server
    Jika server A dan server B di domain yang sama, SID sama digunakan. Oleh karena itu, pengguna tidak mungkin untuk menjadi yatim piatu.
  • Dalam naskah output, login diciptakan dengan menggunakan sandi yang dienkripsi. Hal ini karena argumen HASHED dalam pernyataan membuat LOGIN. Argumen ini menentukan bahwa sandi yang dimasukkan setelah argumen PASSWORD sudah hashed.
  • secara asali, hanya anggota dari peran sysadmin tetap server dapat menjalankan sebuah pilih pernyataan dari tampilan sys.server_principals . Kecuali anggota sysadmin tetap peran server memberikan izin yang diperlukan untuk pengguna, pengguna tidak dapat membuat atau menjalankan output script.
  • Langkah-langkah dalam artikel ini tidak mentransfer informasi database default untuk login tertentu. Hal ini karena default database mungkin tidak selalu ada pada server B. Untuk mendefinisikan default database untuk login, menggunakan LOGIN mengubah pernyataan oleh lewat di nama untuk masuk dan database standar sebagai argumen.
  • Case-sensitive server A dan case-sensitive server B: urutan menurun server A mungkin case-sensitive, dan urutan menurun server B mungkin case-sensitive. Dalam kasus ini, pengguna harus ketik password di semua huruf setelah Anda mentransfer login dan password untuk contoh pada server B.

    Case-sensitive server A dan case-sensitive server B: urutan menurun server A mungkin case-sensitive, dan urutan menurun server B mungkin case-sensitive. Dalam kasus ini, pengguna tidak dapat masuk dengan menggunakan login dan password yang Anda transfer ke contoh pada server B kecuali salah satu dari kondisi berikut ini benar:
    • Password asli berisi huruf tidak.
    • Semua Surat-surat di password asli adalah huruf.
    Case-sensitive atau case-sensitive pada kedua server: pengurutan server A dan server B mungkin case-sensitive, atau urutan menurun server A dan server B mungkin case-sensitive. Dalam kasus ini, pengguna tidak mengalami masalah.
  • Login yang sudah dalam contoh pada server B mungkin memiliki nama yang sama dengan nama dalam naskah output. Dalam kasus ini, Anda menerima pesan galat berikut ketika Anda menjalankan skrip output pada contoh pada server B:
    MSG 15025, tingkat 16, negara bagian 1, baris 1
    Kepala server 'MyLogin' sudah ada.
    Demikian pula, sebuah login yang sudah dalam contoh pada server B mungkin memiliki SID yang sama sebagai SID dalam naskah output. Dalam kasus ini, Anda menerima pesan galat berikut ketika Anda menjalankan skrip output pada contoh pada server B:
    MSG 15433, tingkat 16, negara bagian 1, baris 1
    Parameter disediakan sid yang digunakan.
    Oleh karena itu, Anda harus melakukan hal berikut:
    1. Meninjau output script dengan hati-hati.
    2. Memeriksa isi dari pandangan sys.server_principals dalam contoh pada server B.
    3. alamat penyuratan ini pesan kesalahan yang sesuai.
  • Dalam SQL Server 2005, SID untuk login digunakan untuk mengimplementasikan database-tingkat akses. Login mungkin memiliki SIDs berbeda dalam database yang berbeda pada server. Dalam kasus ini, login hanya dapat mengakses database yang memiliki SID yang cocok SID di tampilan sys.server_principals . Masalah ini dapat terjadi jika dua database digabung dari server yang berbeda. Untuk mengatasi masalah ini, secara manual menghapus login dari database yang memiliki SID ketidakcocokan dengan menggunakan pernyataan DROP pengguna. Kemudian, tambahkan login lagi dengan menggunakan pernyataan membuat USER.

Referensi

Untuk informasi lebih lanjut tentang bagaimana memecahkan masalah pengguna yatim piatu, pergi ke Memecahkan masalah pengguna yatim piatu Website Microsoft Developer Network (MSDN).

Untuk informasi lebih lanjut tentang pernyataan membuat LOGIN, pergi ke MEMBUAT LOGIN (Transact-SQL) MSDN website.

Untuk informasi lebih lanjut tentang pernyataan mengubah LOGIN, pergi ke MENGUBAH LOGIN (Transact-SQL) MSDN website.

Properti

ID Artikel: 918992 - Kajian Terakhir: 03 Agustus 2013 - Revisi: 2.0
Berlaku bagi:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
Kata kunci: 
kbsqlsetup kbexpertiseadvanced kbhowto kbinfo kbmt KB918992 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan oleh perangkat lunak penerjemahan mesin Microsoft, dan mungkin telah diedit oleh Masyarakat Microsoft melalui teknologi CTF dan bukan oleh seorang penerjemah profesional. Microsoft menawarkan baik artikel yang diterjemahkan oleh manusia maupun artikel hasil editan terjemahan oleh mesin/komunitas, sehingga Anda dapat mengakses semua artikel di Sentra Pengetahuan yang kami miliki dalam berbagai bahasa. Namun artikel hasil editan mesin atau bahkan komunitas tidak selalu sempurna. Artikel ini dapat mengandung kesalahan dalam hal kosa kata, sintaksis atau tatabahasa, sangat mirip dengan penutur asing yang membuat kekeliruan ketika berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab atas ketidakakuratan, kesalahan atau kerugian apa pun akibat dari kekeliruan dalam penerjemahan isi atau penggunaannya oleh pelanggan kami. Microsoft juga akan senantiasa memperbarui perangkat lunak penerjemahan mesin dan alat untuk menyempurnakan Editan Hasil Penerjemahan Mesin.
Klik disini untuk melihat versi Inggris dari artikel ini: 918992

Berikan Masukan

 

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