Cloner un login SQL Server avec ses permissions
Générer un script pour cloner un login, ses rôles, utilisateurs et permissions de base avec cinq procédures stockées.
Cloner un login SQL Server avec ses rôles serveur, ses permissions serveur, ses utilisateurs de base, ses appartenances de rôles et ses permissions de base est une opération fréquente (nouveau collaborateur, compte de remplacement). Cet article décrit l'approche de cinq procédures stockées publiée sur tidbytez.com ; le code complet est disponible sur cette page.
Principe
Les procédures ne modifient rien : elles génèrent un script T-SQL (un jeu de résultats d'instructions) que l'on relit puis exécute. Elles s'installent dans une base utilitaire (dans le script d'origine, le texte DATABASE_PLACEHOLDER_NAME est à remplacer par le nom de cette base).
| Procédure | Rôle |
|---|---|
CloneLogin |
Génère CREATE LOGIN (Windows ou SQL), puis recopie les rôles serveur (sp_addsrvrolemember) et les permissions serveur (serveur, logins, endpoints, etc.) du login source. |
CreateUserInDB |
Génère CREATE USER ... FROM LOGIN dans chaque base en ligne (ou une seule) où le login source a un utilisateur. |
GrantUserRoleMembership |
Génère les sp_addrolemember pour reproduire les rôles de base du login source. |
CloneDBPerms |
Génère les permissions de base : base, schémas, objets, colonnes, utilisateurs, clés symétriques et asymétriques, certificats (avec WITH GRANT OPTION si nécessaire). |
CloneLoginAndAllDBPerms |
Procédure principale qui appelle les quatre autres et renvoie le script complet. |
Paramètres de la procédure principale
| Paramètre | Description |
|---|---|
@NewLogin |
Nom du nouveau login. |
@NewLoginPwd |
Mot de passe (utilisé uniquement pour un login SQL). |
@WindowsLogin |
'T' pour un login Windows, sinon un login SQL avec mot de passe est généré. |
@LoginToClone |
Login source dont on copie les droits. |
@DatabaseName |
Base cible ; NULL pour toutes les bases en ligne. |
Exemple d'utilisation
EXEC [dbo].[CloneLoginAndAllDBPerms]
@NewLogin = new_app_user
,@NewLoginPwd = '<PASSWORD>'
,@WindowsLogin = N'F'
,@LoginToClone = 'existing_app_user'
,@DatabaseName = NULL;
Le résultat est une liste d'instructions du type suivant, à vérifier puis à exécuter :
USE [AppDb];
IF EXISTS (SELECT name FROM sys.database_principals WHERE name = 'existing_app_user')
BEGIN
CREATE USER [new_app_user] FROM LOGIN [new_app_user];
END;
Remarques
- Relire le script généré avant exécution, surtout pour un login clonant des droits élevés.
- Pour un login Windows,
@LoginToClonedoit être le nom complet (DOMAINE\compte) tel qu'il apparaît danssys.server_principals. - Le mot de passe ne doit jamais être stocké dans un script versionné.
- Le code d'origine appartient à son auteur : le télécharger depuis la source indiquée plutôt que de le recopier.