ÇÔ¸Õ¡ÒöèÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹ÃÐËÇèÒ§ÍÔ¹Êáµ¹«ì¢Í§ SQL Server

ÇÔ¸Õ¡ÒöèÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹ÃÐËÇèÒ§à«ÔÃì¿àÇÍÃì·ÕèÃѹ SQL Server 7.0

ÇÔ¸Õ¡ÒöèÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹¨Ò¡ SQL Server 7.0 ä» Âѧ SQL Server 2000 ËÃ×ÍÃÐ ËÇèÒ§à«ÔÃì¿àÇÍÃì·ÕèÃѹ SQL Server 2000

1. àª×èÍÁµè͡Ѻà«ÔÃì¿àÇÍÃì»ÅÒ·ҧ¢Í§ SQL Server 2000 ÂéÒÂä»ÂѧºÃÔ¡ÒáÒÃá»Å§¢éÍÁÙÅã¹µÑǨѴ¡ÒÃͧ¤ì¡Ã¢Í§ SQL Server ¢ÂÒÂâ¿Åà´ÍÃì ¤ÅÔ¡¢ÇÒᾤࡨ·éͧ¶Ôè¹áÅéÇ ¤ÅԡᾤࡨãËÁè.
2. ËÅѧ¨Ò¡ DTS á¾ç¤à¡¨à»Ô´µÑÇÍ͡Ẻ ¤ÅÔ¡¡ÒÃâ͹ÂéÒ¡ÒÃÅçÍ¡ÍÔ¹¢Í§§Ò¹ã¹¡ÒçҹàÁ¹Ù ¡ÃÍ¡ÃÒÂÅÐàÍÕ´à¡ÕèÂǡѺ¡ÒÃáËÅè§:,»ÅÒ·ҧáÅÐÅçÍ¡ÍÔ¹á·çºµÒÁ¤ÇÒÁàËÁÒÐÊÁ
   
   ÊÔè§ÊӤѭà«ÔÃì¿àÇÍÃì»ÅÒ·ҧ¢Í§ SQL Server 2000 äÁèÊÒÁÒöàÃÕ¡ãªéÃØè¹ 64 ºÔµ¢Í§ SQL Server 2000 ¤ÍÁâ¾à¹¹µì DTS ÃØè¹ 64 ºÔµ¢Í§ SQL Server 2000 äÁèÁÕÍÂÙè ¶éҤس¡ÓÅѧ¹Óà¢éÒÅçÍ¡ÍÔ¹¨Ò¡ÍÔ¹Êáµ¹«ì¢Í§ SQL Server ·ÕèÍÂÙ躹¤ÍÁ¾ÔÇàµÍÃì·ÕèᡵèÒ§ËÒ¡ ¢Í§ÍÔ¹Êáµ¹«ì¢Í§ SQL Server ¨Ðµéͧ·Ó§Ò¹ÀÒÂãµéºÑ­ªÕâ´àÁ¹ã¹¡Ò÷ӧҹ
   
   ËÁÒÂà˵Ø:ÇÔ¸Õ DTS ¨Ðâ͹ÂéÒ¡ÒÃÃËÑʼèÒ¹áµèäÁè SID à´ÔÁ ¶éÒäÁèÁÕÊÃéÒ§ÅçÍ¡ÍÔ¹ â´Âãªé SID à´ÔÁ áÅаҹ¢éÍÁÙżÙéãªé¨Ð¶Ù¡â͹ÂéÒÂä»Âѧà«ÔÃì¿àÇÍÃìãËÁèÂѧ °Ò¹¢éÍÁÙżÙéãªé¨ÐÊÒÁÒö orphaned ¨Ò¡ÅçÍ¡ÍÔ¹ àÁ×è͵éͧ¡ÒöèÒÂâ͹ SID µé¹©ºÑº áÅСÒÃàÅÕ觼èÒ¹¼Ùéãªé orphaned ·ÓµÒÁ¢Ñ鹵͹ã¹Êèǹ "ÇÔ¸Õ¡ÒÃá¡é䢷Ñé§ËÁ´à¾×èÍÅçÍ¡ÍÔ¹¢Í§¡ÒÃâ͹ÂéÒÂáÅÐÃËÑʼèÒ¹ÃÐËÇèÒ§ÃØè¹µèÒ§ æ ¢Í§ SQL Server"

ÇÔ¸Õ¡ÒöèÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹ÃÐËÇèÒ§ÍÔ¹Êáµ¹«ì¢Í§ SQL Server 2005

¤ÇÒÁÅÐàÍÕ´·ÕèÊÁºÙóìà¾×èÍâ͹ÂéÒ¡ÒÃà¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹ÃÐËÇèÒ§ÃØè¹µèÒ§ æ ¢Í§ SQL Server

• ʤÃÔ»µìã¹ÇÔ¸Õ¡ÒõèÍ仹ÕéÊÃéÒ§¡Ãкǹ¡ÒèѴà¡çºÊͧ·ÕèÁÕª×è͹Õésp_hexadecimal¢Ñ鹵͹¡ÒèѴà¡çº áÅСÒÃsp_help_revlogin¢Ñ鹵͹㹡ÒèѴà¡çº¢Í§¤Ø³ÇҧἹËÅÑ¡database.
• ʤÃÔ»µì¨Ð¢Öé¹ÍÂÙè¡ÑºµÒÃÒ§Ãкº SQL Server â¤Ã§ÊÃéÒ§¢Í§µÒÃÒ§àËÅèÒ¹ÕéÍÒ¨à»ÅÕè¹á»Å§ÃÐËÇèÒ§ÃØ蹢ͧ SQL Server ¡ÒÃàÅ×Í¡â´ÂµÃ§¨Ò¡µÒÃÒ§Ãкº¤×Í discouraged
• µÃǨ·Ò¹ remarks 㹵͹·éÒ¢ͧº·¤ÇÒÁ¹ÕéÊÓËÃѺ¢éÍÁÙÅà¡ÕèÂǡѺ¢Ñ鹵͹ã¹ÇÔ¸Õ¡Ò÷ÕèÊӤѭ
• ÇÔ¸Õ·Õè 2 ¡Ó˹´ÅçÍ¡ÍÔ¹ä»Âѧ˹éÒ·Õè

ÇÔ¸Õ·Õè 1:

• ¤Ø³¶èÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹¨Ò¡ SQL Server 7.0 ä»Âѧ SQL Server 7.0
• ¤Ø³¶èÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹¨Ò¡ SQL Server 7.0 ä»Âѧ SQL Server 2000
• ¤Ø³¶èÒÂâ͹à¢éÒÊÙèÃкºáÅÐÃËÑʼèÒ¹ÃÐËÇèÒ§à«ÔÃì¿àÇÍÃì·ÕèÃѹ SQL Server 2000

1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating thesp_help_revlogin¡Ãкǹ§Ò¹·Õèà¡çºäÇé

   ----- 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. After you create thesp_help_revloginstored procedure, run thesp_help_revloginprocedure from Query Analyzer on the source server. ¡Ãкǹ¡ÒÃsp_help_revloginstored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of thesp_help_revloginstored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. µÑÇÍÂèÒ§::

   EXEC master..sp_help_revlogin
   

ÇÔ¸Õ·Õè 2

• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.

1. Run the following script on the source 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. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.

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

Remarks

• Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by thesp_help_revloginprocedure, and replace the domain name with the new domain in thesp_grantloginãºá¨é§ÂÍ´ Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
• After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
  Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
  For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
  240872

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

  ÇÔ¸Õ¡ÒÃá¡éä¢ÊÔ·¸Ôì㹡ÒõѴÊÔ¹¤éÒ¨Ò¡¤ÅѧàÁ×èͤسÂéÒ°ҹ¢éÍÁÙÅÃÐËÇèÒ§à«ÔÃì¿àÇÍÃì·ÕèÃѹ SQL Server
  For instructions about using thesp_change_users_loginstored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
  274188

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

  "Troubleshooting Orphaned Users" topic in Books Online is incomplete
• If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
  314546

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

  How to move databases between computers that are running SQL Server
• You can do this because of the@encryptopt¾ÒÃÒÁÔàµÍÃì㹡ÒÃsp_addloginsystem stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
• By default, only members of the sysadminfixed server role can select from thesysxloginsµÒÃÒ§: Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
• ÇÔ¸Õ¡ÒùÕéäÁèä´éÅͧ¡ÒöèÒÂâ͹°Ò¹¢éÍÁÙÅàÃÔèÁµé¹ÊÓËÃѺ¡ÒÃà¢éÒÊÙèÃкºà©¾ÒÐä´éà¹×èͧ¨Ò¡°Ò¹¢éÍÁÙÅ¡ÒÃàÃÔèÁµé¹ÍÒ¨äÁèàÊÁÍÁÕÍÂÙ躹à«ÔÃì¿àÇÍÃì»ÅÒ·ҧ àÁ×è͵éͧ¡ÒáÓ˹´°Ò¹¢éÍÁÙÅàÃÔèÁµé¹ÊÓËÃѺ¡ÒÃà¢éÒÊÙèÃкºà»ç¹ ¤Ø³ÊÒÁÒöãªé¡ÒÃsp_defaultdbÃкºà¡çº¡Ãкǹ¡Òà â´Â¡ÒÃÊ觼èÒ¹¹Ñ鹪×èÍÅçÍ¡ÍÔ¹áÅаҹ¢éÍÁÙÅ¡ÒÃàÃÔèÁµé¹à»ç¹ÍÒÃì¡ÔÇàÁ¹µì ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁà¡ÕèÂǡѺ¡ÒÃãªé¡Ãкǹ¡ÒùÕé ãËé´Ù·ÕèËÑÇ¢éÍ "sp_defaultdb" ã¹ SQL Server ˹ѧÊ×ÍÍ͹äŹì
• ã¹ÃÐËÇèÒ§¡ÒÃâ͹ÂéÒ¢ͧÅçÍ¡ÍÔ¹ÃÐËÇèÒ§ÍÔ¹Êáµ¹«ì¢Í§ SQL Server ¶éÒÅӴѺ¡ÒÃàÃÕ§¢Í§à«ÔÃì¿àÇÍÃìµé¹·Ò§¤×Í case-insensitive áÅÐÅӴѺ¡ÒÃàÃÕ§¢Í§à«ÔÃì¿àÇÍÃì»ÅÒ·ҧ¤×Í case-sensitive ¤Ø³µéͧ»é͹ÍÑ¡¢ÃÐ alphabetical ·Ñé§ËÁ´ã¹ÃËÑʼèÒ¹à»ç¹ÍÑ¡¢ÃеÑǾÔÁ¾ìãË­èËÅѧ¨Ò¡¡ÒÃâ͹ÂéÒ¢ͧÅçÍ¡ÍÔ¹ä»Âѧà«ÔÃì¿àÇÍÃì»ÅÒ·ҧ ¶éÒÅӴѺ¡ÒÃàÃÕ§¢Í§à«ÔÃì¿àÇÍÃìµé¹·Ò§ case-sensitive áÅÐÅӴѺ¡ÒÃàÃÕ§¢Í§à«ÔÃì¿àÇÍÃì»ÅÒ·ҧà»ç¹ case-insensitive ¤Ø³¨ÐäÁèÊÒÁÒöà¢éÒÊÙèÃкº ´éÇ¡ÒÃÅçÍ¡ÍÔ¹¢Í§¡ÒÃâ͹ÂéÒÂâ´Âãªé¢Ñ鹵͹ outlined 㹺·¤ÇÒÁ¹Õé àÇé¹áµè ÇèÒäÁèÁÕÍÑ¡¢ÃÐ alphabetical »ÃСͺ´éÇÂÃËÑʼèÒ¹à´ÔÁ ËÃ×ÍàÇé¹áµè ÇèÒÍÑ¡¢ÃÐ alphabetical ·Ñé§ËÁ´ã¹ÃËÑʼèÒ¹à´ÔÁ·ÕèÁÕÍÑ¡¢ÃеÑǾÔÁ¾ìãË­è ¶éÒà«ÔÃì¿àÇÍÃì·Ñé§ÊͧÁÕ case-sensitive ËÃ×Íà«ÔÃì¿àÇÍÃì·Ñé§ÊͧÁÕ case-insensitive ¤Ø³¨ÐäÁ辺»Ñ­ËÒ¹Õé ¹Õè¤×ÍÅѡɳоÔàÈɢͧ´éÒ¹¢Í§ÅѡɳÐÇèÒ SQL Server ¨Ñ´¡ÒÃÃËÑʼèÒ¹ ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé´Ù·ÕèËÑÇ¢éÍ "ãºÅѡɳоÔàÈÉã¹ÃËÑʼèÒ¹¢Í§à»ÅÕè¹àÃÕ§ÊÑè§" ã¹ SQL Server 7.0 ˹ѧÊ×ÍÍ͹äŹì
• àÁ×èͤسàÃÕ¡ãªé¨Ò¡¹Ñé¹sp_help_revloginʤÃÔ»µìº¹à«ÔÃì¿àÇÍÃì»ÅÒ·ҧ ËÒ¡à«ÔÃì¿àÇÍÃì»ÅÒ·ҧÁÕÍÂÙèáÅéÇà¢éÒÊÙèÃкº·Õè¡Ó˹´ ´éǪ×èÍà´ÕÂǡѹà¢éÒÊÙèÃкºã¹Ê¤ÃÔ»µì¡ÒÃáÊ´§¼ÅÍÂèÒ§ã´ÍÂèҧ˹Öè§ ¤Ø³ÍÒ¨¾º¢éͼԴ¾ÅÒ´µèÍ仹ÕéàÁ×èÍ¡ÒôÓà¹Ô¹¡Òâͧ¼ÅÅѾ¸ì¢Í§¡ÒÃsp_help_revloginʤÃÔ»µì:
  à«ÔÃì¿àÇÍÃì: ¢èÒÇÊÒÃà¡ÕèÂǡѺ 15025 ÃдѺ 16 ʶҹР1, sp_addlogin ¡Ãкǹ§Ò¹ ºÃ÷Ѵ 56
  ¡ÒÃà¢éÒÊÙèÃкº 'test1' ÍÂÙèáÅéÇ
  likewise ¶éÒ¡ÒÃà¢éÒÊÙèÃкºÍ×è¹·ÕèÁÕÍÂÙè ´éÇ SID ¤èÒà´ÕÂǺ¹à«ÔÃì¿àÇÍÃì¹Õéà»ç¹·Õè¤Ø³¡ÓÅѧ¾ÂÒÂÒÁà¾ÔèÁ ¤Ø³ä´éÃѺ¢éͤÇÒÁáÊ´§¢éͼԴ¾ÅÒ´µèÍ仹Õé:
  ¢èÒÇÊÒÃà¡ÕèÂǡѺà«ÔÃì¿àÇÍÃì: 15433 ÃдѺ 16 ʶҹР1, sp_addlogin ¡Ãкǹ§Ò¹ ºÃ÷Ѵ 93
  ¾ÒÃÒÁÔàµÍÃì·ÕèãËéÁÒ @ sid ¶Ù¡ãªé§Ò¹
  ´Ñ§¹Ñé¹ ¤Ø³µéͧÍÂèÒ§Ãͺ¤Íº·º·Ç¹¤ÓÊÑè§àËÅèÒ¹Õéä´é¨Ò¡ µÃǨÊͺà¹×éÍËҢͧsysxloginsµÒÃÒ§ áÅШѴ¡ÒâéͼԴ¾ÅÒ´àËÅèÒ¹ÕéµÒÁÅӴѺ
• ÁÕãªé¤èÒ SID ÊÓËÃѺ¡ÒÃà¢éÒÊÙèÃкºà©¾ÒÐà»ç¹¢éÍÁÙž×é¹°Ò¹ÊÓËÃѺ¡ÒÃãªé¡ÒÃà¢éÒ¶Ö§ÃдѺ¢Í§°Ò¹¢éÍÁÙÅã¹ SQL Server ´Ñ§¹Ñé¹ ¶éÒÁÕ¡ÒÃà¢éÒÊÙèÃкºà´ÕÂǡѹä´éÊͧ¤èÒ·ÕèᵡµèÒ§¡Ñ¹ÊÓËÃѺ SID ·ÕèÃдѺ°Ò¹¢éÍÁÙÅ (㹡Òðҹ¢éÍÁÙÅᵡµèÒ§¡Ñ¹Êͧº¹à«ÔÃì¿àÇÍÃì¹Ñé¹), ÅçÍ¡ÍÔ¹¨Ðà·èÒ¹Ñé¹ÊÒÁÒöà¢éÒ¶Ö§°Ò¹¢éÍÁÙŹÑé¹ SID µÃ§¡Ñº¤èÒã¹sysloginsÊÓËÃѺ¡ÒÃÅçÍ¡ÍÔ¹ ʶҹ¡Òóìàªè¹ÍÒ¨à¡Ô´¢Öé¹ä´é¶éÒ°Ò¹¢éÍÁÙÅÊͧ·ÕèÊͺ¶ÒÁä´é¶Ù¡ÃÇÁºÑ­ªÕ¨Ò¡à«ÔÃì¿àÇÍÃì·ÕèᵡµèÒ§¡Ñ¹Êͧ àÁ×è͵éͧ¡ÒÃá¡é䢻ѭËÒ¹Õé à¢éÒÊÙèÃкºáººÊͺ¶ÒÁ¨Ð¨Óà»ç¹µéͧ¶Ù¡àÍÒÍÍ¡¨Ò¡°Ò¹¢éÍÁÙÅ·ÕèÁÕ SID ·ÕèäÁèµÃ§¡Ñ¹ â´Âãªé´éǵ¹àͧsp_dropuser¢Ñ鹵͹¡ÒèѴà¡çº áÅéÇ à¾ÔèÁÍÕ¡¤ÃÑé§ â´Âãªé¡ÒÃsp_adduser¡Ãкǹ§Ò¹·Õèà¡çºäÇé

¡ÅѺ仴éÒ¹º¹ | ãËé¢éÍàʹÍá¹Ð