Vous êtes actuellement hors ligne, en attente de reconnexion à Internet.

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

INTRODUCTION
Cet article explique comment faire pour transférer des noms d'accès et des mots de passe entre instances de Microsoft SQL Server 2005 sur différents serveurs.

Pour plus d'informations sur la façon de transférer des noms d'accès et des mots de passe entre instances d'autres versions de SQL Server 2005, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
246133 Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server
Plus d'informations
Dans cet article, le serveur A et le serveur B sont des serveurs différents. En outre, les serveurs A et B exécutent tous deux SQL Server 2005.

Après le déplacement d'une base de données de l'instance de SQL Server sur le serveur A vers l'instance de SQL Server sur le serveur B, les utilisateurs ne peuvent peut-être pas se connecter à la base de données sur le serveur B. De plus, le message d'erreur suivant peut s'afficher pour les utilisateurs :
Échec de la connexion pour l'utilisateur 'MyUser'. (Microsoft SQL Server, Erreur : 18456)
Ce problème se produit car vous n'avez pas transféré les noms d'accès et les mots de passe de l'instance de SQL Server sur le serveur A vers l'instance de SQL Server sur le serveur B.

Pour transférer les noms d'accès et les mots de passe de l'instance de SQL Server sur le serveur A vers l'instance de SQL Server sur le serveur B, procédez comme suit :
  1. Sur le serveur A, démarrez SQL Server Management Studio, puis connectez-vous à l'instance de SQL Server à partir de laquelle vous avez déplacé la base de données.
  2. Ouvrez une Nouvelle fenêtre d'éditeur de requête, puis exécutez le script suivant.
    USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @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_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs  DEALLOCATE login_curs  RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO
    Remarque Ce script crée deux procédures stockées dans la base de données master. Ces deux procédures stockées sont appelées sp_hexadecimal et sp_help_revlogin.
  3. Exécutez l'instruction suivante.
    EXEC sp_help_revlogin
    Le script de sortie qui est généré par la procédure stockée sp_help_revlogin est le script d'ouverture de session. Le script d'ouverture de session d'origine crée les noms d'accès qui comportent l'identificateur de sécurité (SID) d'origine et le mot de passe d'origine.
  4. Sur le serveur B, démarrez SQL Server Management Studio, puis connectez-vous à l'instance de SQL Server vers laquelle vous avez déplacé la base de données.

    Important Avant de passer à l'étape 5, lisez les informations de la section « Remarques ».
  5. Ouvrez une Nouvelle fenêtre d'éditeur de requête, puis exécutez le script de sortie qui est généré à l'étape 3.

Notes

Lisez les informations suivantes avant d'exécuter le script de sortie sur l'instance sur le serveur B :
  • Lisez attentivement le script de sortie. Si le serveur A et le serveur B figurent dans des domaines différents, vous devez modifier le script de sortie. Vous devez ensuite remplacer le nom de domaine d'origine par le nouveau nom de domaine dans les instructions CREATE LOGIN. Les noms d'accès intégrés qui disposent de droits d'accès dans le nouveau domaine n'ont pas le même SID que les noms d'accès du domaine d'origine. Par conséquent, les utilisateurs de la base de données se retrouvent orphelins de ces noms d'accès. Pour plus d'informations sur la façon de résoudre ce problème d'utilisateurs orphelins, cliquez sur le numéro ci-dessous pour afficher l'article correspondant 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
    Si le serveur A et le serveur B figurent dans le même domaine, le même SID est utilisé. Par conséquent, il est peu probable que les utilisateurs soient orphelins.
  • Dans le script de sortie, les noms d'accès sont créés à l'aide du mot de passe chiffré. Ceci est dû à l'argument HASHED dans l'instruction CREATE LOGIN. Cet argument indique que le mot de passe qui est entré après l'argument PASSWORD est déjà haché.
  • Par défaut, seul un membre du rôle de serveur fixe sysadmin peut exécuter une instruction SELECT à partir de la vue sys.server_principals. À moins qu'un membre du rôle de serveur fixe sysadmin n'accorde les autorisations nécessaires aux utilisateurs, ces derniers ne peuvent pas créer ou exécuter le script de sortie.
  • Les étapes de cet article ne permettent pas de transférer les informations de base de données par défaut pour un nom d'accès particulier. Ceci est dû au fait que la base de données par défaut n'existe pas toujours sur le serveur B. Pour définir la base de données par défaut pour un nom d'accès, utilisez l'instruction ALTER LOGIN en transmettant le nom d'accès et la base de données par défaut comme arguments.
  • L'ordre de tri du serveur A peut ne pas respecter la casse alors que l'ordre de tri du serveur B la respecte. Dans ce cas, les utilisateurs doivent taper toutes les lettres des mots de passe en majuscules après le transfert des noms d'accès et des mots de passe à l'instance sur le serveur B.

    Inversement, l'ordre de tri du serveur A peut respecter la casse alors que l'ordre de tri du serveur B ne la respecte pas. Dans ce cas, les utilisateurs ne peuvent pas se connecter à l'aide des noms d'accès et des mots de passe que vous avez transférés à l'instance sur le serveur B, à moins que l'une des conditions suivantes soit vérifiée :
    • Les mots de passe d'origine ne contiennent pas de lettres.
    • Toutes les lettres des mots de passe d'origine sont en majuscules.
    L'ordre de tri à la fois du serveur A et du serveur B respecte la casse ou au contraire ne la respecte pas. Dans tous ces cas, les utilisateurs ne rencontrent aucun problème.
  • Un nom d'accès qui figure déjà sur l'instance sur le serveur B doit avoir le même nom que celui qui se trouve dans le script de sortie. Dans ce cas, le message d'erreur suivant s'affiche lorsque vous exécutez le script de sortie sur l'instance sur le serveur B :
    Msg 15025, Niveau 16, État 1, Ligne 1
    L'entité de sécurité du serveur  'MonNomAccès' existe déjà.
    De même, un nom d'accès qui figure déjà sur l'instance sur le serveur B doit avoir le même SID que celui qui se trouve dans le script de sortie. Dans ce cas, le message d'erreur suivant s'affiche lorsque vous exécutez le script de sortie sur l'instance sur le serveur B :
    Msg 15433, Niveau 16, État 1, Ligne 1
    La valeur du paramètre sid est utilisée.
    Par conséquent, vous devez effectuer les opérations suivantes :
    1. Lisez attentivement le script de sortie.
    2. Examinez le contenu de la vue sys.server_principals dans l'instance sur le serveur B.
    3. Résolvez ces messages d'erreur comme il convient.
  • Dans SQL Server 2005, le SID d'un nom d'accès donné est utilisé comme base de l'implémentation de l'accès de niveau base de données. Un nom d'accès peut avoir deux SID différents dans deux bases de données différentes sur un serveur. Dans ce cas, le nom d'accès peut uniquement accéder à la base de données qui contient le SID qui correspond au SID de la vue sys.server_principals. Ce problème peut se produire si les deux bases de données sont consolidées à partir de deux serveurs différents. Pour résoudre ce problème, supprimez manuellement le nom d'accès de la base de données qui contient un SID non concordant à l'aide de l'instruction DROP USER. Ajoutez ensuite de nouveau le nom d'accès à l'aide de l'instruction CREATE USER.
Références
Pour plus d'informations sur la façon de dépanner les utilisateurs orphelins, reportez-vous au site Web MSDN (Microsoft Developer Network) à l'adresse suivante (en anglais) : Pour plus d'informations sur l'instruction CREATE LOGIN, reportez-vous au site Web de MSDN à l'adresse suivante (en anglais) : Pour plus d'informations sur l'instruction ALTER LOGIN, reportez-vous au site Web de MSDN à l'adresse suivante (en anglais) :
Propriétés

ID d'article : 918992 - Dernière mise à jour : 07/12/2013 10:40:00 - Révision : 4.1

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • kbsqlserverengine kbexpertiseadvanced kbhowto kbinfo KB918992
Commentaires