Cara mentransfer login dan password antara contoh untuk SQL Server

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 cara mentransfer login dan password antara contoh-contoh lain versi SQL Server, klik nomor artikel berikut ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:

Pada artikel ini, server a dan b adalah server yang berbeda. Selain itu, kedua server a dan b adalah 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 untuk contoh SQL Server pada server B, pengguna mungkin tidak dapat masuk ke database pada server B. Selain itu, pengguna akan menerima pesan galat berikut:Masalah ini terjadi karena Anda lakukan tidak mentransfer login dan password dari contoh SQL Server pada server a contoh SQL Server pada server B.

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

Metode 1: Log in dengan menggunakan pra-SQL Server 2000 password

Untuk mengatasi masalah ini, minta pengguna untuk log in 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 sandi.

Metode 2: Reset password di SQL Server

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

Catatan Password hashing algoritma ini digunakan ketika Anda me-reset password.

Metode 3: Membuat log dalam naskah yang memiliki sandi kosong

Buat log in script yang memiliki sandi kosong, ikuti langkah berikut:

1. Pada server A, mulai SQL Server Management Studio, dan kemudian tautan langsung ke contoh SQL Server yang Anda pindah database.
2. Buka jendela Query Editor 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 disimpan prosedur dalam master database. Prosedur yang bernama sp_hexadecimal dan sp_help_revlogin.
3. Jalankan pernyataan berikut:

   EXEC sp_help_revlogin

   Script output yang menghasilkan sp_help_revlogin disimpan prosedur adalah naskah masuk. Script login ini menciptakan login yang memiliki Identifier keamanan asli (SID) dan sandi asli.
4. Pada server B, mulai SQL Server Management Studio, dan kemudian tautan langsung ke contoh SQL Server yang Anda pindah database.
   
   Penting Sebelum Anda pergi ke langkah 5, meninjau informasi dalam bagian "Keterangan".
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 sebuah baru SQL Server 2012 login dengan menggunakan pra-SQL Server 2000 login yang scripted, Anda akan menerima galat berikut ini:
  MSG 15021, tingkat 16, negara bagian 2, garis jatuh berseri 1
  Nilai tidak sah yang diberikan untuk parameter PASSWORD. Menetapkan nilai parameter berlaku.
  Catatan Anda menerima kesalahan ini pada tahun 2012 SQL Server 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, menciptakan sebuah 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 percobaan login berikutnya.
• Sandi dapat hashed dalam tiga cara:
  • VERSION_LEGACY: hash ini adalah 16-byte pra-SQL Server 2000 hash.
  • VERSION_SHA1: ini hash yang dihasilkan dengan menggunakan algoritma SHA1 dan digunakan dalam SQL Server 2000 melalui SQL Server 2008 R2.
  • VERSION_SHA2: ini hash yang dihasilkan dengan menggunakan algoritma SHA2 512 dan digunakan dalam SQL Server 2012.
• Dalam SQL Server 2008 R1 dan versi sebelumnya, hashes sandi pra-SQL Server 2000 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 ada pada 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 b di domain yang berbeda, Anda harus mengubah output script. Kemudian, Anda harus mengganti nama domain aslinya 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 yang asli. Oleh karena itu, pengguna yang yatim piatu dari login ini. Untuk informasi lebih lanjut tentang cara mengatasi ini yatim pengguna, klik nomor artikel berikut ini untuk melihat artikel di dalam Pangkalan Pengetahuan Microsoft:
  240872

  (http://support.microsoft.com/kb/240872/ )

  Cara mengatasi masalah izin ketika Anda memindahkan database antara server yang menjalankan SQL Server
  Jika server a dan b di domain yang sama, SID sama digunakan. Oleh karena itu, pengguna tidak mungkin untuk menjadi yatim piatu.
• Dalam naskah output, login yang dibuat dengan menggunakan sandi yang dienkripsi. Hal ini karena argumen HASHED dalam pernyataan membuat LOGIN. Argumen ini menunjukkan bahwa password yang masuk setelah argumen PASSWORD sudah hashed.
• secara asali, hanya anggota peran server tetap sysadmin dapat menjalankan pernyataan pilih dari tampilan sys.server_principals . Kecuali anggota sysadmin tetap peran server hibah 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 menentukan standar database untuk login, menggunakan LOGIN mengubah pernyataan oleh lewat di nama untuk masuk dan database standar sebagai argumen.
• Case-insensitive server a dan case-sensitive B: urutan menurun server a mungkin case-insensitive, 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-insensitive B: urutan menurun server a mungkin case-sensitive, dan urutan menurun server b mungkin case-insensitive. Dalam kasus ini, pengguna tidak dapat login dengan menggunakan login dan password yang Anda transfer ke contoh pada server b kecuali salah satu dari kondisi berikut ini benar:
  • Sandi asli berisi surat-surat tidak ada.
  • Semua surat-surat di sandi asli adalah huruf.
  Case-sensitive atau case-insensitive pada kedua server: dengan urutan menurun kedua server a dan b mungkin case-sensitive, atau dengan urutan menurun kedua server a dan b mungkin case-insensitive. 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 output script pada contoh pada server B:
  MSG 15025, tingkat 16, negara bagian 1, baris 1
  Server utama '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 output script pada contoh pada server B:
  MSG 15433, tingkat 16, negara bagian 1, baris 1
  Parameter disediakan sid adalah digunakan.
  Oleh karena itu, Anda harus melakukan hal berikut:
  1. Meninjau output script dengan hati-hati.
  2. Memeriksa isi dari tampilan sys.server_principals dalam contoh ini di 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 di berbagai database pada server. Dalam kasus ini, login hanya dapat mengakses database yang memiliki SID yang cocok SID dalam tampilan sys.server_principals . Masalah ini dapat terjadi jika database dua gabungan dari server yang berbeda. Untuk mengatasi masalah ini, secara manual menghapus login dari database yang memiliki ketidakcocokan SID dengan menggunakan pernyataan DROP pengguna. Kemudian, tambahkan login lagi dengan menggunakan pengguna membuat pernyataan.

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

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.