Les ouvertures de session utilisateur et les autorisations sur une base de données peuvent être incorrectes après la restauration de la base de données


Symptômes


Si un vidage d’une base de données utilisateur de SQL Server est restauré à un SQL Server différent (par exemple, un serveur de sauvegarde à chaud) ou à la même de SQL Server après le rechargement d’une ancienne version de la base de données master ou reconstruction, les ouvertures de session utilisateur et les autorisations sur la base de données est peut-être incorrectes.

Ce problème peut se révéler de plusieurs façons :
  • Lors de la connexion à un serveur 6.x, les utilisateurs peuvent recevoir le message d’erreur suivant :
    Msg 4002, niveau 14, état 1, serveur de Microsoft SQL Server, ligne 0
    Échoué de la connexion
    DB-Library : Ouverture de session incorrecte.
  • Lors de la connexion à un serveur 7.0, les utilisateurs peuvent recevoir le message d’erreur suivant :
    Msg 18456, niveau 14, état 1,
    Échec de la connexion pour l’utilisateur '%ls'.
  • Lors de l’accès aux objets dans la base de données, les utilisateurs peuvent recevoir le message d’erreur suivant :
    Msg 229, niveau 14, état 1
    %s l’autorisation refusée sur l’objet %. * s, base de données %. * s, propriétaire %.*s
  • Lors de la création d’une connexion et accorder l’accès à la base de données restaurée ou ajouter l’utilisateur à la base de données, le message d’erreur suivant peut s’afficher :
    Microsoft SQL-DMO (ODBC SQLState : 42000) erreur 15023 : utilisateur ou le rôle « %s » existe déjà dans la base de données en cours.
  • Les utilisateurs devront peut-être des autorisations sur les objets pour lesquels ils précédemment n’ont pas.

Cause


Informations d’ouverture de session de l’utilisateur sont stockées dans la table syslogins de la base de données master. En modifiant les serveurs, ou en modifiant ces informations par la reconstruction ou la restauration d’une ancienne version de la base de données master, les informations peuvent différer à partir de laquelle le vidage de la base de données utilisateur a été créé. Si les ouvertures de session n’existent pas pour les utilisateurs, qu’ils recevront une erreur indiquant « Échec de la connexion » lors de la tentative de connexion au serveur. Si les ouvertures de session utilisateur existent, mais les valeurs SUID (pour 6.x) ou les valeurs d’identificateur de sécurité (pour 7.0) dans master... syslogins et la table sysusers de la base de données utilisateur diffèrent, les utilisateurs peuvent avoir des autorisations différentes que prévu dans la base de données de l’utilisateur.

Remarque Si vous utilisez Microsoft SQL Server 2005, la table syslogins et la table sysusers sont implémentés comme des vues de compatibilité. Ces vues sont sys.syslogins et sys.sysusers. Pour plus d’informations sur les vues de compatibilité, consultez la rubrique « Vues de compatibilité (Transact-SQL) » dans la documentation en ligne de SQL Server 2005.

Solution de contournement


Pour contourner ce problème, effectuez l’une des opérations suivantes :
  • Si les scripts en cours sont disponibles pour ajouter les autorisations, les utilisateurs et les ouvertures de session, supprimer et les recréer à partir de scripts. Pour des exemples d’utilisation de scripts pour le transfert des connexions entre les serveurs, consultez l’article suivant de la Base de connaissances Microsoft :
    246133 Comment : transférer des noms d’accès et des mots de passe entre Instances de SQL Server

    240872 comment résoudre les problèmes d’autorisations lorsqu’une base de données est déplacée entre les serveurs SQL
  • Vous pouvez utiliser la procédure stockée sp_change_users_login pour associer les relations entre les tables syslogins, sysusers et sysalternates. Toutefois, la procédure effectue des estimations les plus précises sur les liaisons et peut permettre à un utilisateur les privilèges d’accès plus que prévu. Exécution de la procédure avec l’option de rapport tout d’abord pour générer une liste d’utilisateurs qui sera modifié. Par la suite, vous devez vérifier pour vous assurer que les utilisateurs disposent des autorisations appropriées. En outre, sachez que la procédure sp_change_users_login ne résout pas les problèmes d’autorisation dérivés de connexions et les utilisateurs créés dans un ordre différent sur la base de données où la sauvegarde est restaurée.
  • Restaurer un vidage de la base de données master à partir de l’heure du vidage de base de données utilisateur sur le serveur avant le chargement de la base de données de l’utilisateur. Cette opération garantit que toutes les informations utilisateur dans la base de données de l’utilisateur correspond à correctement avec la table syslogins dans master.


    Avertissement: la base de données master contient les informations au niveau du serveur et affecte toutes les bases de données sur le serveur. En restaurant la base de données master, vous pouvez rencontrer des ID utilisateur supplémentaires et/ou des bases de données sont perdus ou ont des autorisations incorrectes. Toutes les modifications dans le masque ont eu lieu depuis le moment de la sauvegarde seront perdues. N’utilisez cette méthode si vous êtes certain que la version de sauvegarde de la base de données master contienne des informations précises pour la base de données en question et toutes les autres bases de données sur le serveur.
  • Utilisez transfert responsable (6.x) ou DTS (pour 7.0) pour copier les connexions. N’oubliez pas que les mots de passe ne seront pas transférées à l’aide de cette méthode.
  • Contactez votre fournisseur d’assistance principal.