Cara mentransfer login dan password antara contoh-contoh SQL Server

Kembali ke atas

Bagaimana untuk mentransfer login dan password antara server yang menjalankan SQL Server 7.0

Kembali ke atas

Cara mentransfer login dan password dari SQL Server 7.0 untuk SQL Server 2000 atau antara server yang menjalankan SQL Server 2000

1. Menyambung ke server tujuan SQL Server 2000, pindah ke layanan transformasi Data dalam SQL Server Enterprise Manager, memperluas folder, klik kanan Paket lokal, lalu klik Paket baru.
2. Setelah DTS paket desainer membuka, klik Mentransfer login tugas pada Tugas menu. Melengkapi informasi tentang Sumber, Tujuan dan Login tab yang sesuai.
   
   Penting SQL Server 2000 server tujuan tidak dapat menjalankan versi 64-bit SQL Server 2000. DTS komponen untuk versi 64-bit SQL Server 2000 tidak tersedia. Jika Anda mengimpor login dari contoh SQL Server yang ada di komputer yang terpisah, Anda contoh SQL Server akan harus berjalan di bawah Account Domain untuk menyelesaikan tugas.
   
   Catatan Metode DTS akan mentransfer password tetapi tidak SID asli. Jika sebuah login tidak dibuat dengan menggunakan SID asli dan pengguna database juga dikirim ke server baru, database pengguna akan yatim piatu dari login. Untuk mentransfer SID asli dan memotong yatim pengguna, ikuti langkah-langkah pada bagian "Resolusi lengkap untuk transfer login dan password antara versi yang berbeda dari SQL Server".

Kembali ke atas

Cara mentransfer login dan password antara contoh-contoh SQL Server 2005

Kembali ke atas

Resolusi lengkap untuk mentransfer login dan password antara versi yang berbeda dari SQL Server

• Skrip di metode berikut membuat dua disimpan prosedur yang diberi sp_hexadecimal disimpan prosedur dan sp_help_revlogin disimpan prosedur dalam Anda Master database.
• Script bergantung pada tabel SQL Server sistem. Struktur tabel ini dapat beralih antara versi SQL Server. Memilih langsung dari sistem tabel kecewa.
• Meninjau pernyataan pada akhir artikel ini untuk penting informasi tentang langkah-langkah pada metode.
• Metode 2 memberikan login untuk peran.

Kembali ke atas

Metode 1

• Anda mentransfer login dan password dari SQL Server 7.0 ke SQL Server 7.0.
• Anda mentransfer login dan password dari SQL Server 7.0 ke SQL Server 2000.
• Mentransfer data login dan password antara server yang menjalankan SQL Server 2000.

1. Menjalankan script berikut pada sumber SQL Server. Lanjutkan ke langkah 2 jika Anda selesai membuatsp_help_revlogin prosedur yang tersimpan.

   ----- Begin Script, Create sp_help_revlogin procedure -----
   
   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(256) OUTPUT
   AS
   DECLARE @charvalue varchar(256)
   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 @xstatus int
   DECLARE @binpwd  varbinary (256)
   DECLARE @txtpwd  sysname
   DECLARE @tmpstr  varchar (256)
   DECLARE @SID_varbinary varbinary(85)
   DECLARE @SID_string varchar(256)
   
   IF (@login_name IS NULL)
     DECLARE login_curs CURSOR FOR 
       SELECT sid, name, xstatus, password FROM master..sysxlogins 
       WHERE srvid IS NULL AND name <> 'sa'
   ELSE
     DECLARE login_curs CURSOR FOR 
       SELECT sid, name, xstatus, password FROM master..sysxlogins 
       WHERE srvid IS NULL AND name = @login_name
   OPEN login_curs 
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
   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 ''
   PRINT 'DECLARE @pwd sysname'
   WHILE (@@fetch_status <> -1)
   BEGIN
     IF (@@fetch_status <> -2)
     BEGIN
       PRINT ''
       SET @tmpstr = '-- Login: ' + @name
       PRINT @tmpstr 
       IF (@xstatus & 4) = 4
       BEGIN -- NT authenticated account/group
         IF (@xstatus & 1) = 1
         BEGIN -- NT login is denied access
           SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
           PRINT @tmpstr 
         END
         ELSE BEGIN -- NT login has access
           SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
           PRINT @tmpstr 
         END
       END
       ELSE BEGIN -- SQL Server authentication
         IF (@binpwd IS NOT NULL)
         BEGIN -- Non-null password
           EXEC sp_hexadecimal @binpwd, @txtpwd OUT
           IF (@xstatus & 2048) = 2048
             SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
           ELSE
             SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
           PRINT @tmpstr
   	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
           SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
             + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
         END
         ELSE BEGIN 
           -- Null password
   	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
           SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
             + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
         END
         IF (@xstatus & 2048) = 2048
           -- login upgraded from 6.5
           SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
         ELSE 
           SET @tmpstr = @tmpstr + '''skip_encryption'''
         PRINT @tmpstr 
       END
     END
     FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
     END
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN 0
   GO
    ----- End Script -----
   
   

2. Setelah Anda membuat sp_help_revlogin disimpan prosedur, menjalankan sp_help_revlogin prosedur dari Query Analyzer pada server sumber. The sp_help_revlogin prosedur yang tersimpan dapat digunakan pada SQL Server 7.0 dan SQL Server 2000. Output dari sp_help_revlogin disimpan prosedur adalah skrip login yang membuat login dengan SID dan sandi asli. Menyimpan output, dan kemudian paste dan menjalankannya dalam permintaan Analyzer pada tujuan SQL Server. Misalnya:

   EXEC master..sp_help_revlogin
   

Kembali ke atas

Metode 2

• Anda mentransfer login dan password dari SQL Server 7.0 ke SQL Server 2005.
• Anda mentransfer login dan password dari SQL Server 2000 ke SQL Server 2005.
• Anda menetapkan login untuk peran.

1. Menjalankan script berikut pada sumber 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(256) OUTPUT 
   AS 
   DECLARE @charvalue varchar(256) 
   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_2000_to_2005') IS NOT NULL 
   DROP PROCEDURE sp_help_revlogin_2000_to_2005 
   GO 
   CREATE PROCEDURE sp_help_revlogin_2000_to_2005 
   
   @login_name sysname = NULL, 
   @include_db bit = 0, 
   @include_role bit = 0 
   
   AS 
   DECLARE @name sysname 
   DECLARE @xstatus int 
   DECLARE @binpwd varbinary (256) 
   DECLARE @dfltdb varchar (256) 
   DECLARE @txtpwd sysname 
   DECLARE @tmpstr varchar (256) 
   DECLARE @SID_varbinary varbinary(85) 
   DECLARE @SID_string varchar(256) 
   
   IF (@login_name IS NULL) 
   DECLARE login_curs CURSOR STATIC FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] <> 'sa' 
   ELSE 
   DECLARE login_curs CURSOR FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] = @login_name 
   
   OPEN login_curs 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   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 '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** CREATE LOGINS *****/' 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF (@xstatus & 4) = 4 
   BEGIN -- NT authenticated account/group 
   IF (@xstatus & 1) = 1 
   BEGIN -- NT login is denied access 
   SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + '''' 
   PRINT @tmpstr 
   END 
   ELSE 
   BEGIN -- NT login has access 
   SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')' 
   PRINT @tmpstr 
   SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS' 
   PRINT @tmpstr 
   END 
   END 
   ELSE 
   BEGIN -- SQL Server authentication 
   EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT 
   
   IF (@binpwd IS NOT NULL) 
   BEGIN -- Non-null password 
   EXEC sp_hexadecimal @binpwd, @txtpwd OUT 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED' 
   END 
   ELSE 
   BEGIN -- Null password 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=''''' 
   END 
   
   SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   
   IF @include_db = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET DEFAULT DATABASES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']' 
   PRINT @tmpstr 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   IF @include_role = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET SERVER ROLES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF @xstatus &16 = 16 -- sysadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &32 = 32 -- securityadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &64 = 64 -- serveradmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &128 = 128 -- setupadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &256 = 256 --processadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &512 = 512 -- diskadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &1024 = 1024 -- dbcreator 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &4096 = 4096 -- bulkadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN 0 
   GO
   
   exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
   GO

2. Menyimpan output, dan kemudian paste dan menjalankan output dalam SQL Server Management Studio pada tujuan SQL Server 2005.

CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID

Kembali ke atas

Komentar

• Meninjau output script dengan hati-hati sebelum Anda menjalankan di tujuan SQL Server. Jika Anda harus mentransfer login ke contoh SQL Server di domain yang berbeda dari contoh sumber SQL Server, mengedit script yang dihasilkan oleh sp_help_revlogin prosedur, dan ganti domain nama domain baru dalamsp_grantlogin pernyataan. Karena login terpadu diberikan akses dalam domain baru tidak akan SID sama sebagai login di domain yang asli, database pengguna akan yatim piatu dari login ini. Untuk mengatasi ini yatim piatu pengguna, lihat artikel yang direferensikan dalam item peluru berikut. Jika Anda mentransfer login terpadu antara contoh dari SQL Server di domain yang sama, SID sama digunakan dan pengguna tidak cenderung menjadi yatim piatu.
• Setelah Anda memindahkan login, pengguna mungkin tidak memiliki izin untuk mengakses database yang telah pindah. Masalah ini digambarkan sebagai "yatim pengguna". Jika Anda mencoba untuk memberikan akses login untuk database, mungkin gagal menunjukkan pengguna sudah ada:
  Microsoft SQL-DMO (ODBC SQLState: 42000) kesalahan 15023: pengguna atau peran '% s' sudah ada di database saat ini.
  Untuk petunjuk tentang cara untuk memetakan login untuk pengguna database untuk menyelesaikan yatim SQL Server login dan terpadu login, Lihat artikel berikut pada Basis Pengetahuan Microsoft:
  240872  (http://support.microsoft.com/kb/240872/ ) Cara mengatasi masalah izin ketika Anda memindahkan database antara server yang menjalankan SQL Server
  Untuk petunjuk tentang menggunakan sp_change_users_login disimpan prosedur untuk memperbaiki pengguna yatim satu-per-satu (ini akan hanya alamat pengguna yatim piatu dari standar SQL login), lihat berikut artikel di dalam Basis Pengetahuan Microsoft:
  274188  (http://support.microsoft.com/kb/274188/ ) "Mengatasi masalah Orphaned pengguna" topik di buku Online tidak lengkap
• Jika transfer login dan password adalah bagian dari langkah database server baru yang menjalankan SQL Server, lihat artikel berikut di Basis Pengetahuan Microsoft untuk deskripsi alur kerja dan langkah-langkah terlibat:
  314546  (http://support.microsoft.com/kb/314546/ ) Bagaimana memindahkan database antara komputer yang menjalankan SQL Server
• Anda dapat melakukan ini karena @ encryptopt parameter dalam sp_addlogin sistem disimpan prosedur, yang memungkinkan masuk untuk dibuat oleh menggunakan sandi yang dienkripsi. Untuk informasi lebih lanjut tentang prosedur ini, lihat "sp_addlogin (T-SQL)" topik dalam SQL Server buku Online.
• Secara default, hanya anggota peran server yang di-sysadminfixed dapat memilih dari sysxlogins tabel. Kecuali anggota peran sysadmin hibah yang diperlukan izin, pengguna akhir tidak dapat membuat atau menjalankan prosedur ini disimpan.
• Pendekatan ini tidak mencoba untuk mentransfer default database informasi untuk login tertentu karena default database mungkin tidak selalu ada pada server tujuan. Untuk menentukan standar database untuk login, Anda dapat menggunakan sp_defaultdb sistem disimpan prosedur melalui nama login dan default database sebagai argumen. Untuk informasi lebih lanjut tentang menggunakan prosedur ini, lihat topik "sp_defaultdb" dalam SQL Server buku Online.
• Selama transfer login antara contoh SQL Server, jika urutan source server case-insensitive dan semacam urutan server tujuan, Anda harus memasukkan semua abjad karakter dalam password sebagai karakter huruf besar setelah transfer dari login ke server tujuan. Jika urutan source server Case-sensitive dan urutan server tujuan case-insensitive, Anda tidak akan dapat untuk login dengan login yang ditransfer menggunakan prosedur yang dijelaskan pada artikel ini, kecuali sandi asli berisi karakter tidak abjad atau kecuali semua karakter alfabetis sandi asli adalah karakter huruf besar. Jika kedua server Case-sensitive atau kedua server case-insensitive, Anda tidak akan mengalami masalah ini. Ini adalah efek samping dari cara yang menangani SQL Server password. Untuk selengkapnya, lihat "efek pada password dari mengubah jenis Perintah"topik dalam SQL Server 7.0 buku Online.
• Ketika Anda menjalankan output dari sp_help_revlogin script di server tujuan, jika server tujuan sudah memiliki login didefinisikan dengan nama yang sama sebagai salah satu login pada script output, Anda dapat melihat setelah kesalahan pada pelaksanaan output sp_help_revlogin script:
  Server: Msg 15025, Level 16, negara bagian 1, prosedur sp_addlogin, baris 56
  Login 'test1' sudah ada.
  Demikian juga, jika ada info masuk lain dengan sama SID nilai pada server ini sebagai yang Anda mencoba untuk menambahkan, Anda menerima pesan galat berikut:
  Server: Msg 15433, Tingkat 16, negara bagian 1, prosedur sp_addlogin, Line 93
  Disediakan parameter @ sid ini digunakan.
  Oleh karena itu, Anda harus hati-hati Review output dari perintah ini, memeriksa isi sysxlogins meja, dan mengatasi kesalahan ini sesuai.
• SID nilai untuk login tertentu digunakan sebagai dasar untuk menerapkan database tingkat akses dalam SQL Server. Oleh karena itu, jika sama login memiliki dua nilai berbeda untuk SID tingkat database (dalam dua berbeda database pada server), login hanya akan memiliki akses ke yang SID yang sesuai nilai dalam database syslogins untuk yang masuk. Situasi seperti ini mungkin terjadi jika kedua database dalam pertanyaan harus konsolidasi dari dua server yang berbeda. Untuk memecahkan masalah ini masalah, login yang bersangkutan harus dihapus secara manual dari database yang telah ketidakcocokan SID dengan menggunakan sp_dropuser disimpan prosedur, dan kemudian ditambahkan lagi dengan menggunakan sp_adduser prosedur yang tersimpan.

Kembali ke atas