Comment générer les scripts de création des utilisateurs et des roles de base de données sous SQL Server

Introduction

Nous recevons une demande de l’équipe de développement demandant de copier les bases de données de production vers l’instance SQL Server de l’environnement de développement, ceci afin de simuler des scénarios de test sur des données récents. Une fois que la sauvegarde de la base de données de production a été restaurée dans l’instance de développement (DEV). Pour ce faire, les anciennes bases de données ont été écrasées lors de la restauration. Il en va de même pour les utilisateurs de ces bases qui ont laissé place à ceux de la base de données de production correspondante. Nous avons cependant encore besoin des comptes de connexions (logins) utilisés précédemment pour pouvoir accéder aux base de données de l’instance de développement (DEV).

Que devrions-nous faire pour sauvegarder ces comptes de connexion, avec leurs permissions, avant de remplacer une ancienne base de données par une version plus récente de celle-ci afin de pouvoir la restaurer à nouveau?

Méthodes de génération des scripts pour les utilisateurs et autorisations

Pour être capable de vous connecter sur un serveur SQL Server, vous devez disposer d’un accès au niveau serveur via un identifiant authentifié soit par SQL Server ou soit par Windows. L’utilisateur d’une base de données fait le lien entre l’identifiant (SID) de cet utilisateur et l’identifiant (SID) d’un compte de connexion existant. Cette correspondance se fera seulement si les valeurs pour la propriété SID sont identiques. Un utilisateur orphelin est un utilisateur auquel aucun compte de connexion n’est associé. Les bases de données à relation contenant-contenu (contained databases) sont une exception: les utilisateurs ont la possibilité de se connecter à la base de données sans s’authentifier au niveau du serveur.

Les comptes de connexion SQL Server et leurs permissions sont stockés dans les tables système du catalogue de sécurité de la base de données master. Pour lister ces identifiants avec leurs rôles assignées, il est nécessaire d’exécuter une requête sur ces tables système.

Dans notre cas, une fois que la base de données de production est restaurée sur notre serveur DEV, les comptes de connexion (logins) correspondant aux utilisateurs existent déjà et étaient mis en correspondance avec les utilisateurs des anciennes base de données. Ils ne sont cependant pas toujours automatiquement reliés aux utilisateurs des bases de données restaurées. Il y a donc une action à effectuer ici.

Pour faire correspondre les utilisateurs de base de données et les comptes de connexion à SQL Servers, il y a deux possibilités:

  1. Faire correspondre manuellement les utilisateurs un par un, cela peut prendre beaucoup de temps
  2. Utiliser T-SQL pour générer le script de création de ces utilisateurs et de leurs permissions à partir des anciennes bases de données de l’environnement de développement avant la restauration. Ce script sera exécuté pour remettre en place les correspondances après la restauration complète des bases de données à partir de la dernière sauvegardée

Les scripts se composent de deux parties; la première est de lister tous les utilisateurs de la base de données, excepté ceux qui sont créés par Microsoft. La requête suivante génère un ensemble d’instructions CREATE USER: 

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
		'U'
		,'S'
		)
	AND NAME NOT IN (
		'dbo'
		,'guest'
		,'sys'
		,'INFORMATION_SCHEMA'
		)

Le résultat de cette requête ressemblera à l’aperçu ci-dessous:

La deuxième partie consiste à générer les appartenances de ces utilisateurs à des rôles de la base de données pour chaque utilisateur à l’aide de l’instruction EXEC sp_AddRoleMember: 

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

L’exécution de la deuxième requête vous donne le résultat ci-dessous:

Maintenant vous avez les scripts pour créer chaque utilisateur au niveau de la base de données, vous devez faire le lien avec le compte de connexion (login) correspondant et garantir l’accès en l’ajoutant dans les rôles de la base de données. La dernière étape consiste à appliquer le script sur chaque base de données après le processus de restauration.

Si vous recevez le message d’erreur ci-dessous pendant que le lien est fait au niveau des accès utilisateurs:

Error 15023: User or role ‘%s’ already exists in the current database.

C’est qu’il un utilisateur du même nom existe déjà dans la base de données que nous venons de restaurer dans l’environnement de développement (et donc également dans l’environnement de poduction) avec un SID différent. C’est normal car cet utilisateur a été initialement créé sur une instance différente de celle sur laquelle nous travaillons. Pour corriger ce problème, il faut utiliser la commande suivante: 

EXEC sp_change_users_login 'Auto_Fix'
	,'YourUser'
GO

Comme vous pouvez le voir, cette méthode demande beaucoup d’efforts pour générer le script T-SQL. De plus cela ne fonctionnera pas dans la situation d’une authentification via SQL Server où nous pouvons avoir un SID différent généré pour le même nom d’utilisateur. Nous devons également posséder une connaissance suffisante des tables et les procédures stockées dites “système” pour pouvoir réaliser et exécuter les requêtes et obtenir les informations sur les utilisateurs.

Pour ne pas faire face à ces inconvénients et générer un script T-SQL pour tous les utilisateurs avec leur autorisations prenant en charge leur authentification avec le bon SID, nous utiliserons l’outil ApexSQL Script, un outil tierce partie pour générer des scripts sur des objets de base de données ainsi que sur les données provenant d’ApexSQL.

Utilisation des ApexSQL Script

ApexSQL Script peut facilement être utilisé pour réaliser des scripts sur des utilisateurs de bases de données avec les autorisations nécessaires en suivant les étapes ci-dessous:

  1. Démarrez l’application ApexSQL Script
  2. Sélectionnez l’onglet Select databases spécifiez le serveur qui héberge la base de données dont vous souhaitez gérer les utilisateurs et spécifiez le type d’authentification nécessaire pour se connecter sur ce serveur. Si vous sélectionnez l’authentification SQL Server, vous devez spécifier le nom d’utilisateur et le mot de passe. Ensuite, cliquez sur Connect pour vous connectez.

  3. Une liste des bases de données se trouvant sur le serveur s’affiche sur la partie droite de l’écran. Choisissez la base de données sur laquelle vous souhaitez réaliser des scripts sur ses utilisateurs (SQLShackDemo dans notre exemple).

  4. Dans l’onglet Structure (voir ci-dessous), choisissez l’option Permissions pour réaliser des scripts sur les utilisateurs avec leurs autorisations.
    Vous pouvez sélectionner Logins with Users si vous avez besoin de générer les commandes de création des comptes de connexion au serveur (logins) pour les utilisateurs de la base de données. Dans notre exemple, nous supposons que ces logins existent déjà dans le serveur DEV, mais nous avons besoin de réaliser des sauvegardes des utilisateurs de la base de données avant de remplacer l’ancienne base de données DEV avec celle de la nouvelle version du serveur de production.

  5. Cliquez sur Open.

  6. Les composants choisis de la base de données seront listés ; à partir d’où vous pourrez sélectionner sur quels utilisateurs vous souhaitez réaliser des scripts. Pour générer le script de création des utilisateurs avec leur appartenance aux rôles, nous sélectionnerons tous les utilisateurs ainsi que tous les rôles dans notre exemple

  7. Cliquez sur Script présent dans le bandeau supérieur de l’onglet Home

  8. Dans la fenêtre de l’assistant qui s’ouvre, choisissez T-SQL comme type de fichier de sortie et cliquez sur Next

  9. Dans la fenêtre des dépendances, cliquez sur Next

  10. Dans la fenêtre Output file window, Onglet General menu déroulant Action, sélectionnez Create and write to file. Ensuite, vous avez le choix de sauvegarder le script à un endroit spécifique ou de l’ouvrir dans un éditeur de script. Choisissez l’option Open script in editor ensuite cliquez sur Create

Le script généré contient des instructions pour créer les utilisateurs sélectionnés et ajouter ces utilisateurs dans les rôles des bases de données qui spécifient leurs permissions dans la base de données. Ce script peut facilement être exécuté:

Si vous avez le même nom d’utilisateur d’authentification SQL dans l’environnement de production et dans l’environnement de développement, vous pouvez générer un script de ce login utilisant le SID de l’environnement de production en choisissant Logins with Users à partir de la fenêtre de structure et les résultats seront comme affichés ci-dessous:

Conclusion

Comme vous pouvez le voir, ApexSQL Script est un outil simple pour vous aider avec ce cas de figure. Il peut être utilisé pour générer des scripts sur des utilisateurs de base de données, avec leur appartenance dans leurs rôles de base de données respectives, ceci en quelques étapes, sans être familiarisé avec les tables des bases de données et les procédures stockées dites “système” ni même la manière de les appeler. Cela permet également de contourner les problèmes de différence de SID au niveau de l’authentification via SQL Server. Pour ce faire, il faut générer le script de création du login correspondant avec le “bon” SID directement sur le serveur source.

Liens utiles

 

Related posts:

  1. Comment utiliser les expressions régulières pour générer des données aléatoires de test sur une instance SQL Server
  2. Techniques de copie et d’export-import en masse avec SQL Server
August 26, 2017