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
- 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;
- 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.
- 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
- Les logins correspondants doivent exister sur le serveur cible, avec le même SID si on veut éviter les utilisateurs orphelins ; les rôles de serveur sont à gérer séparément.
- Le mot de passe des rôles d'application n'est pas récupérable : le script contient un emplacement à remplacer (
<PASSWORD>). sp_addrolememberest déprécié ; sur les versions récentes on peut le remplacer parALTER ROLE ... ADD MEMBER ....