Users temporaires pour database snapshots

Lorsque vous créez un database snapshot (instantané de base de données), les utilisateurs, permissions et appartenance au rôles de bases de données sont issus de la base source et ne peuvent plus être modifiés par la suite, le snapshot étant en lecture seule.
Si vous voulez que certains utilisateurs aient accès à ce snapshot, sans pour autant qu’il aient l’accès à la base source, le seul moyen est d’octroyer temporairement des permissions, juste pour la création du snapshot. Cela devient vite une opération lourde à gérer et à maintenir…

Le script qui suit permet d’automatiser ce processus sur un ensemble de bases de données. Avant la création du snapshot, il effectue :

  • La création des users (pour des logins existants)
  • L’ajout de leur appartenance à des rôles de base de données

Une fois le snapshot créé, il permet d’effectuer l’opération inverse :

  • Supprimer les users créés

Pour installer l’ensemble des objets, se positionner dans la base de données msdb (ou une autre base de données d’administration)

3 tables sont créées (qui contiendront la configuration à appliquer), ainsi que les 4 procédures stockées suivantes :

  • TemporaryPermissionsAdd : permet d’ajouter un jeu de permissions à un login
  • TemporaryPermissionsRemove : permet de retirer un jeu de permissions à un login, mêmes paramètres
  • TemporaryPermissionsReport : permet d’afficher le jeu de permissions configurées

Pour ces 3 premières procédures, les paramètres sont : @LoginName sysname, @DatabaseName sysname, @RoleName sysname, @JobName VARCHAR(30)
(le paramètre jobname permet d’établir plusieurs profils de configuration différents)

  • TemporaryPermissionsSetAll : permet d’octroyer ou de retirer les permissions, prend les paramètres suivants :
    • @action = ‘ADD’ ou ‘REMOVE’
    • @jobname = nom du profil de configuration
    • @test : si not NULL, mode de test sans effectuer l’opération