LLI Wiki — My Digital Knowledge Base

SQL Server : générer le script des utilisateurs, rôles et permissions d'une base

Copier la sécurité d'une base vers une autre en générant un script T-SQL avec la procédure usp_get_object_permissions.

Pour copier la sécurité d'une base SQL Server vers une autre (par exemple après une restauration sur un autre serveur), on peut générer un script T-SQL qui recrée les utilisateurs, rôles et permissions de la base source. Une procédure connue pour cela est usp_get_object_permissions, publiée sur SQLServerCentral : Reverse engineer database permissions (2005 up). Le code n'est pas reproduit ici ; voir la page source pour le récupérer et vérifier sa licence.

Utilisation

  1. Créer la procédure dans la base source, puis l'exécuter depuis cette base :
USE AppDb;
GO
EXEC dbo.usp_get_object_permissions;
  1. La procédure ne modifie rien : elle renvoie plusieurs jeux de résultats dont chaque ligne est une instruction T-SQL. Copier ces lignes, les relire, puis les exécuter dans la base cible.
  2. Supprimer la procédure quand elle n'est plus utile (DROP PROCEDURE dbo.usp_get_object_permissions).

Ce que le script génère, dans l'ordre

Jeu de résultats Contenu
Propriétaire ALTER AUTHORIZATION ON DATABASE::... pour rétablir le propriétaire de la base
Suppression d'utilisateurs DROP USER pour les utilisateurs (hors comptes système) ; pas toujours nécessaire
Création d'utilisateurs CREATE USER ... FOR LOGIN ... avec le schéma par défaut ; les utilisateurs orphelins ne sont pas recréés
Rôles de base CREATE ROLE pour les rôles définis par l'utilisateur
Rôles d'application CREATE APPLICATION ROLE avec un mot de passe à renseigner
Membres de rôles EXEC sp_addrolemember pour chaque membre
Permissions sur objets et colonnes GRANT / DENY (avec WITH GRANT OPTION si besoin)
Permissions sur schémas GRANT / DENY sur SCHEMA::...
VIEW DEFINITION Permissions de niveau base de données

Remarques

Mastodon LLIMastodon SocialFosstodon