Cach chuyn thng tin ng nhp va mt khu gia cac phin ban cua SQL Server 2005 va SQL Server 2008

D?ch tiu ? D?ch tiu ?
ID c?a bi: 918992 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

GII THIU

Bai vit nay m ta cach chuyn thng tin ng nhp va mt khu gia cac phin ban cua SQL Server 2005 va Microsoft SQL Server 2008 trn cac may chu khac nhau.

bit thm thng tin v cach chuyn thng tin ng nhp va mt khu gia cac phin ban khac cua SQL Server, bm vao s bai vit sau y xem bai vit trong C s Kin thc Microsoft:
246133 Cach chuyn thng tin ng nhp va mt khu gia cac phin ban cua SQL Server

THNG TIN THM

Trong bai vit nay, may chu A va may chu B la cac may chu khac nhau. Ngoai ra, ca may chu A va may chu B ang chay SQL Server 2005.

Chu y Thng tin nay cung ap dung cho SQL Server 2008.

Sau khi ban di chuyn c s d liu t phin ban SQL Server trn may chu A sang phin ban SQL Server trn may chu B, ngi dung co th khng ng nhp c vao c s d liu trn may chu B. Ngoai ra, ngi dung co th nhn c thng bao li sau:
ng nhp khng thanh cng cho ngi dung 'MyUser'. (Microsoft SQL Server, Li: 18456)
S c nay xay ra do ban khng chuyn thng tin ng nhp va mt khu t phin ban SQL Server trn may chu A sang phin ban SQL Server trn may chu B.

chuyn thng tin ng nhp va mt khu t phin ban SQL Server trn may chu A sang phin ban SQL Server trn may chu B, hay lam theo cac bc sau:
  1. Trn may chu A, khi ng SQL Server Management Studio, sau o kt ni vi phin ban SQL Server ma t o ban di chuyn c s d liu.
  2. M ca s Query Editor mi, sau o chay tp lnh sau.
    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)
    BT U
      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)
    BT U
      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)
    BT U
      IF (@@fetch_status <> -2)
      BT U
        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 )
            BT U
              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
            END
            IF ( @is_expiration_checked IS NOT NULL )
            BT U
              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
    
    Chu y Tp lnh nay tao hai quy trinh c lu tr trong c s d liu chinh. Hai quy trinh c lu tr c t tn la quy trinh c lu tr sp_hexadecimal va quy trinh c lu tr sp_help_revlogin.
  3. Chay cu lnh sau.
    EXEC sp_help_revlogin
    Tp lnh xut c tao bi quy trinh c lu tr sp_help_revlogin la tp lnh ng nhp. Tp lnh ng nhp nay tao thng tin ng nhp co Ma inh dang Bao mt (SID) gc va mt khu gc.
  4. Trn may chu B, khi ng SQL Server Management Studio, sau o kt ni vi phin ban SQL Server ma t o ban di chuyn c s d liu.

    Quan trong Trc khi ban chuyn n bc 5, hay xem lai thng tin trong phn "Ghi chu".
  5. M ca s Query Editor mi, sau o chay tp lnh xut c tao trong bc 3.

Ghi chu

Xem lai thng tin sau trc khi ban chay tp lnh xut trong phin ban trn may chu B:
  • Xem lai ky lng tp lnh xut. Nu may chu A va may chu B cac min khac nhau, ban phai sa i tp lnh xut. Sau o, ban phai thay th tn min gc bng tn min mi trong cac cu lnh CREATE LOGIN. Thng tin ng nhp tich hp c cp quyn truy nhp trong min mi khng co cung SID nh thng tin ng nhp trong min gc. Do o, ngi dung bi tach bit vi thng tin ng nhp nay. bit thm thng tin v cach giai quyt nhng ngi dung m ci nay, hay bm vao s bai vit sau y xem bai vit trong C s Kin thc Microsoft:
    240872 Cach giai quyt cac vn v quyn khi ban di chuyn c s d liu gia cac may chu ang chay SQL Server
    Nu may chu A va may chu B trong cung mt min, SID ging nhau c s dung. Do o, ngi dung khng th bi m ci.
  • Trong tp lnh xut, thng tin ng nhp c tao bng mt khu c ma hoa. iu nay la do i s BM trong cu lnh CREATE LOGIN. i s nay chi inh rng mt khu c nhp sau i s MT KHU a bi bm.
  • Theo mc inh, chi thanh vin cua vai tro may chu c inh sysadmin co th chay cu lnh SELECT t ch xem sys.server_principals. Tr khi thanh vin cua vai tro may chu c inh sysadmin cp cac quyn cn thit cho ngi dung, ngi dung khng th tao hoc chay tp lnh xut.
  • Cac bc trong bai vit nay khng chuyn thng tin c s d liu mc inh cho mt ln ng nhp cu th. o la do c s d liu mc inh co th lun tn tai trn may chu B. xac inh c s d liu mc inh cho mt ln ng nhp, s dung cu lnh ALTER LOGIN bng cach chuyn vao tn ng nhp va c s d liu mc inh di dang i s.
  • Th t sp xp cua may chu A co th khng phn bit ch hoa ch thng va th t sp xp cua may chu B co th phn bit ch hoa ch thng. Trong trng hp nay, ngi dung phai nhp tt ca cac ch cai trong mt khu di dang ch cai hoa sau khi ban chuyn thng tin ng nhp va mt khu vao phin ban trn may chu B.

    Hoc th t sp xp cua may chu A co th phn bit ch hoa ch thng va th t sp xp cua may chu B co th khng phn bit ch hoa ch thng. Trong trng hp nay, ngi dung khng th ng nhp bng thng tin ng nhp va mt khu ma ban chuyn vao phin ban trn may chu B tr khi mt trong cac iu kin sau y ung:
    • Mt khu gc khng cha cac ch cai.
    • Tt ca cac ch cai trong mt khu gc la ch cai hoa.
    Th t sp xp cua ca may chu A va may chu B co th phn bit ch hoa ch thng hoc th t sp xp cua ca may chu A va may chu B co th khng phn bit ch hoa ch thng. Trong cac trng hp nay, ngi dung khng gp s c.
  • Thng tin ng nhp a trong phin ban trn may chu B co th co tn ging vi tn trong tp lnh xut. Trong trng hp nay, ban nhn c thng bao li sau khi chay tp lnh xut trong phin ban trn may chu B:
    Thng bao li 15025, Mc 16, Trang thai 1, Dong 1
    Ngi uy nhim may chu 'MyLogin' a tn tai.
    Tng t, thng tin ng nhp a trong phin ban trn may chu B co th co SID ging vi SID trong tp lnh xut. Trong trng hp nay, ban nhn c thng bao li sau khi chay tp lnh xut trong phin ban trn may chu B:
    Thng bao li 15433, Mc 16, Trang thai 1, Dong 1
    Sid tham s c cung cp ang c s dung.
    Do o, ban phai thc hin nhng vic sau:
    1. Xem lai ky lng tp lnh xut.
    2. Kim tra ni dung cua ch xem sys.server_principals trong phin ban trn may chu B.
    3. Giai quyt cac thng bao li nay tng ng.
  • Trong SQL Server 2005, SID cho mt ln ng nhp c s dung lam c s trin khai truy nhp cp c s d liu. Thng tin ng nhp co th co hai SID khac nhau trong hai c s d liu khac nhau trn may chu. Trong trng hp nay, thng tin ng nhp chi co th truy nhp c s d liu co SID khp vi SID trong ch xem sys.server_principals. S c nay chi co th xay ra nu hai c s d liu c hp nht t hai may chu khac nhau. giai quyt s c nay, xoa thng tin ng nhp khoi c s d liu co SID khng trung khp theo cach thu cng bng cach s dung cu lnh DROP USER. Sau o, thm lai thng tin ng nhp bng cach s dung cu lnh CREATE USER.

THAM KH?O

bit thm thng tin v cach khc phuc s c ngi dung m ci, hay ghe thm Web site Mang li Nha phat trin Microsoft (MSDN):
http://msdn2.microsoft.com/en-us/library/ms175475.aspx
bit thm thng tin v cu lnh CREATE LOGIN, hay ghe thm Web site sau cua MSDN:
http://msdn2.microsoft.com/en-us/library/ms189751.aspx
bit thm thng tin v cu lnh ALTER LOGIN, hay ghe thm Web site sau cua MSDN:
http://msdn2.microsoft.com/en-us/library/ms189828.aspx

Thu?c tnh

ID c?a bi: 918992 - L?n xem xt sau cng: 17 Thang Chin 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
T? kha:
kbsqlsetup kbexpertiseadvanced kbhowto kbinfo KB918992

Cung cp Phan hi

 

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