LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon