Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 246133 - Voir les produits auxquels s'applique cet article
Cet article peut contenir des liens vers des informations en langue anglaise (pas encore traduites).
Agrandir tout | Réduire tout

Sommaire

Résumé

Après avoir déplacé des bases de données vers un nouveau serveur, les utilisateurs peuvent ne pas être en mesure d'ouvrir une session sur le nouveau serveur. Dans ce cas, le message d'erreur suivant s'affiche :
Msg 18456, Niveau 16, État 1
Échec de la connexion de l'utilisateur « %ls ».
Vous devez transférer les noms d'accès et les mots de passe vers le nouveau serveur. Cet article explique comment transférer des noms d'accès et des mots de passe vers un nouveau serveur.

Comment faire pour transférer des noms d'accès et des mots de passe entre serveurs SQL Server 7.0

La fonctionnalité de transfert d'objets DTS (Data Transformation Services) de SQL Server 7.0 transfère les noms d'accès et les utilisateurs entre deux serveurs, mais elle ne transfère pas les mots de passe des noms d'accès authentifiés SQL Server. Pour transférer des noms d'accès et des mots de passe d'un serveur SQL Server 7.0 vers un autre serveur qui exécute SQL Server 7.0, suivez les étapes décrites dans la section « Procédure complète pour transférer des noms d'accès et des mots de passe entre différentes versions de SQL Server ».

Comment faire pour transférer des noms d'accès et des mots de passe entre SQL Server 7.0 et SQL Server 2000 ou entre serveurs SQL Server 2000

Pour transférer des noms d'accès et des mots de passe à partir d'un serveur SQL Server 7.0 vers une instance de SQL Server 2000, ou entre deux instances de SQL Server 2000, vous pouvez utiliser le nouveau package DTS Tâche de transfert de connexions dans SQL Server 2000. Pour ce faire, procédez comme suit :
  1. Connectez-vous au serveur de destination SQL Server 2000, accédez aux services DTS dans SQL Server Enterprise Manager, développez le dossier, cliquez avec le bouton droit sur Lots locaux, puis cliquez sur Nouveau lot.
  2. Une fois le concepteur de lots DTS ouvert, cliquez sur Tâche de transfert de connexions dans le menu Tâche. Fournissez les informations demandées dans les onglets Source, Destination et Noms d'accès.

    Important Le serveur de destination SQL Server 2000 ne peut pas exécuter la version 64 bits de SQL Server 2000. Les composants DTS pour la version 64 bits de SQL Server 2000 ne sont pas disponibles. Si vous importez des noms d'accès à partir d'une instance de SQL Server exécutée sur un autre ordinateur, votre instance de SQL Server doit toujours s'exécuter sous un compte de domaine pour que vous puissiez effectuer cette tâche.

    Remarque La méthode DTS transférera les mots de passe mais pas le SID d'origine. Si un nom d'accès n'est pas créé avec le SID d'origine et que les bases de données utilisateur sont également transférées vers un nouveau serveur, les utilisateurs de la base de données se retrouvent orphelins de nom d'accès. Pour transférer le SID d'origine et ignorer les utilisateurs orphelins, suivez les étapes décrites dans la section « Procédure complète pour transférer des noms d'accès et des mots de passe entre différentes versions de SQL Server ».

Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server 2005

Pour plus d'informations sur la façon de transférer des noms d'accès et des mots de passe entre instances de SQL Server 2005, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
918992 Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server 2005

Procédure complète pour transférer des noms d'accès et des mots de passe entre différentes versions de SQL Server

Cette méthode s'applique aux scénarios suivants :
  • Transfert de noms d'accès et de mots de passe entre instances de SQL Server 7.0.
  • Transfert de noms d'accès et de mots de passe entre SQL Server 7.0 et SQL Server 2000.
  • Transfert de noms d'accès et de mots de passe entre SQL Server 7.0 et SQL Server 2005.
  • Transfert de noms d'accès et de mots de passe entre serveurs exécutant SQL Server 2000.
  • Transfert de noms d'accès et de mots de passe entre SQL Server 2000 et SQL Server 2005.
Remarque Passez en revue les notes situées à la fin de cet article ; elles fournissent des informations importantes sur les étapes suivantes.

Pour transférer des noms d'accès et des mots de passe entre différentes versions de SQL Server, procédez comme suit :
  1. Exécutez le script suivant sur le serveur SQL Server source. Ce script crée deux procédures stockées nommées sp_hexadecimal et sp_help_revlogin dans votre base de données master. Une fois la procédure créée, passez à l'étape 2.

    Remarque La procédure suivante dépend des tables système SQL Server. La structure de ces tables peut changer d'une version à l'autre de SQL Server, aussi la sélection directe parmi les tables système est-elle déconseillée.
    ----- 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. Après avoir créé la procédure stockée sp_help_revlogin, exécutez la procédure sp_help_revlogin à partir de l'Analyseur de requêtes du serveur source. La procédure stockée sp_help_revlogin peut être utilisée sur SQL Server 7.0 et SQL Server 2000. La procédure stockée sp_help_revlogin produit des scripts de connexion qui créent des noms d'accès avec le SID et le mot de passe d'origine. Enregistrez la sortie, puis collez-la dans l'Analyseur de requêtes du serveur SQL Server de destination et exécutez-la. Par exemple :
    EXEC master..sp_help_revlogin
    

Notes

  • Examinez attentivement le script obtenu avant de l'exécuter sur le serveur SQL Server de destination. Si vous devez transférer des noms d'accès vers une instance de SQL Server exécutée sur un domaine différent de celui de l'instance source de SQL Server, modifiez le script généré par la procédure sp_help_revlogin et remplacez le nom de domaine par celui du nouveau domaine dans les instructions sp_grantlogin. Dans la mesure où les noms d'accès intégrés disposant de droits d'accès dans le nouveau domaine n'auront pas le même SID que les noms d'accès du domaine d'origine, les utilisateurs de la base de données se retrouveront orphelins de ces noms d'accès. Pour résoudre ces utilisateurs orphelins, consultez les articles référencés sous le paragraphe suivant. Si vous transférez des noms d'accès intégrés entre instances de serveurs SQL Server du même domaine, le même SID est utilisé et l'utilisateur n'est pas susceptible de devenir orphelin.
  • Une fois que vous avez déplacé les noms d'accès, un utilisateur peut ne plus avoir les autorisations nécessaires pour accéder aux bases de données qui ont aussi été déplacées. Ce problème est connu sous l'expression "utilisateur orphelin". Si vous essayez d'accorder l'accès de connexion à la base de données, l'opération peut échouer avec un message indiquant que l'utilisateur existe déjà :
    Microsoft SQL-DMO (ODBC SQLState : 42000) Erreur 15023 : L'utilisateur ou le rôle « %s » existe déjà dans la base de données en cours.
    Pour obtenir des instructions sur le mappage des noms d'accès vers les utilisateurs de la base de données et la résolution des noms d'accès SQL Server orphelins et des noms d'accès intégrés, consultez l'article suivant dans la Base de connaissances Microsoft :
    240872 Comment faire pour résoudre les problèmes d'autorisation lorsque vous déplacez une base de données entre des serveurs SQL Server
    Pour obtenir des instructions sur l'utilisation de la procédure stockée sp_change_users_login en vue de corriger les utilisateurs orphelins un à un (ce qui ne concerne que les utilisateurs orphelins de noms d'accès SQL standard), consultez l'article suivant dans la Base de connaissances Microsoft :
    274188 La rubrique « Dépannage des utilisateurs orphelins » de la documentation en ligne est incomplète
  • Si le transfert des noms d'accès et des mots de passe s'inscrit dans le cadre du déplacement de bases de données vers un nouveau serveur exécutant SQL Server, consultez l'article suivant dans la Base de connaissances Microsoft pour obtenir une description du déroulement des opérations et des étapes requises :
    314546 Comment faire pour déplacer des bases de données entre des ordinateurs qui exécutent SQL Server
  • Cette opération est rendue possible par le paramètre @encryptopt de la procédure stockée système sp_addlogin, qui autorise la création d'un nom d'accès à l'aide du mot de passe chiffré. Pour plus d'informations sur cette procédure, consultez la rubrique "sp_addlogin (T-SQL)" de la documentation en ligne de SQL Server.
  • Par défaut, seuls les membres du rôle serveur sysadminfixed peuvent effectuer des sélections dans la table sysxlogins. À moins qu'un membre du rôle sysadmin n'accorde les autorisations nécessaires, les utilisateurs finals ne peuvent pas créer ou exécuter ces procédures stockées.
  • Cette approche n'essaie pas de transférer les informations de la base de données par défaut d'un nom d'accès particulier car la base de données par défaut peut ne pas exister sur le serveur de destination. Pour définir la base de données par défaut d'un nom d'accès, vous pouvez utiliser la procédure stockée système sp_defaultdb en lui passant le nom d'accès et la base de données par défaut comme arguments. Pour plus d'informations sur l'utilisation de cette procédure, consultez la rubrique "sp_defaultdb" de la documentation en ligne de SQL Server.
  • Lors du transfert de noms d'accès entre des instances de SQL Server, si l'ordre de tri du serveur source ne respecte pas la casse alors que l'ordre de tri du serveur de destination la respecte, vous devez entrer tous les caractères alphabétiques des mots de passe en majuscules après le transfert des noms d'accès vers le serveur de destination. Si l'ordre de tri du serveur source respecte la casse alors que l'ordre de tri du serveur de destination ne la respecte pas, vous ne pourrez pas ouvrir de session avec les noms d'accès transférés à l'aide de la procédure décrite dans cet article, à moins que le mot de passe d'origine ne contienne pas de caractère alphabétique ou que tous les caractères alphabétiques du mot de passe d'origine soient des caractères majuscules. Si les deux serveurs respectent la casse ou si les deux ne la respectent pas, le problème ne se pose pas. Il s'agit d'un effet secondaire de la façon dont SQL Server traite les mots de passe. Pour plus d'informations, consultez la rubrique "Effect on Passwords of Changing Sort Orders" de la documentation en ligne de SQL Server 7.0 (en anglais).
  • Lorsque vous exécutez la sortie du script sp_help_revlogin sur le serveur de destination, si ce serveur a déjà un nom d'accès identique à l'un de ceux de la sortie du script, le message d'erreur suivant peut s'afficher à l'exécution de la sortie du script sp_help_revlogin :
    Serveur : Msg 15025, Niveau 16, État 1, Procédure sp_addlogin, Ligne 56
    L'accès 'test1' existe déjà.
    De même, s'il existe un nom d'accès différent avec la même valeur SID sur ce serveur que celle que vous essayez d'ajouter, le message d'erreur suivant s'affiche :
    Serveur : Msg 15433, Niveau 16, État 1, Procédure sp_addlogin, Ligne 93
    La valeur du paramètre @sid est utilisée.
    Par conséquent, vous devez attentivement examiner la sortie de ces commandes, examiner le contenu de la table sysxlogins et corriger ces erreurs comme il se doit.
  • La valeur SID d'un nom d'accès donné est utilisée comme base de l'implémentation de l'accès de niveau base de données dans SQL Server. Par conséquent, si un même nom d'accès a deux valeurs SID différentes au niveau des bases de données (dans deux bases de données distinctes sur ce serveur), le nom d'accès ne pourra accéder qu'à la base de données dans laquelle le SID correspond à la valeur figurant dans syslogins pour ce nom d'accès. Une telle situation peut se produire si les deux bases de données en question ont été consolidées à partir de deux serveurs différents. Pour résoudre ce problème, le nom d'accès en question doit être supprimé manuellement de la base de données présentant une correspondance de SID incorrecte à l'aide de la procédure stockée sp_dropuser, puis rajouté en utilisant la procédure stockée sp_adduser.

Propriétés

Numéro d'article: 246133 - Dernière mise à jour: dimanche 15 mai 2011 - Version: 9.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2000 Édition Personelle
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Édition Développeur
  • Microsoft SQL Server 2000 Édition Entreprise
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Mots-clés : 
kbsqlserverengine kbhowtomaster kbinfo KB246133
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

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