Discussion :

[DevBaldwin]

Bonjour,

Je suis à la recherche d'une commande magique, qui permettrait de supprimer tous les membres d'un rôle de base de données en une commande, dans l'objectif final de supprimer le rôle en question.
Précision, le rôle en question est une création, il ne s'agit pas des rôles par défaut de la Bdd.

Un genre de " sp_droprolemember 'Nom_du_Role'; " où on aurait fait exprès d'oublier le user_name, pour que cela supprimer tous les membres.

Ça existe, ou je peux encore rêver ?

[SQLpro]

En sql dynamique c'est un script de 4 commandes, du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
DECLARE @SQL NVARCHAR(max) = N'';
SELECT N'ALTER ROLE [' + R.name + N'] DROP MEMBER [' + U.name + N'];'
FROM   sys.database_principals AS R
       JOIN [sys].[database_role_members] AS RM ON R.principal_id = RM.role_principal_id
	   JOIN sys.database_principals AS U ON RM.member_principal_id = U.principal_id
WHERE  R.name = 'Nom_du_Role'
SET @SQL = @SQL + 'DROP ROLE [Nom_du_Role];'
EXEC (@SQL);

A +

[DevBaldwin]

Salut SQLpro,

Merci pour ta réponse.
Le bout de Rq fonctionne très bien (en ajoutant un USE [Bdd] et en insérant le nom du rôle évidemment ).
Par contre, j'aimerais comprendre comment cela fonctionne, pour ne pas le réutiliser bêtement.

J'ai cru voir que la syntaxe « N'' » devant une chaine de caract. servait à forcer le moteur de Bdd à interpréter la chaine en Unicode.
Est-ce tjrs vrai dans la déclaration de ta variable @SQL ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DECLARE @SQL NVARCHAR(max) = N'';

Ou alors, est-ce que ce « N » sert-il à autre chose ?


Également, je crois comprendre que tu concatènes des ordres "ALTER ROLE + DROP MEMBER" (un par membre dans le rôle) dans ta variable @SQL ; que tu suffixes après avec un "DROP ROLE".
Si c'est bien cela, je ne parviens à comprendre à quel moment les "ALTER ROLE + DROP MEMBER" viennent alimenter la variable @SQL.


Saurais-tu m'éclairer ?

[aieeeuuuuu]

bonjour,

J'ai cru voir que la syntaxe « N'' » devant une chaine de caract. servait à forcer le moteur de Bdd à interpréter la chaine en Unicode.
Est-ce tjrs vrai dans la déclaration de ta variable @SQL ?

Oui, c'est bien le cas.

Si c'est bien cela, je ne parviens à comprendre à quel moment les "ALTER ROLE + DROP MEMBER" viennent alimenter la variable @SQL.

C'est normal, elle ne l'est pas

il manque l'affectation dans la requete.

donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL += N'ALTER ROLE [' + R.name + N'] DROP MEMBER [' + U.name + N'];'
FROM   sys.database_principals AS R
       JOIN [sys].[database_role_members] AS RM ON R.principal_id = RM.role_principal_id
	   JOIN sys.database_principals AS U ON RM.member_principal_id = U.principal_id
WHERE  R.name = 'Nom_du_Role'
SET @SQL = @SQL + 'DROP ROLE [Nom_du_Role];'
print (@SQL);

(J'ai aussi remplacé le EXEC par un PRINT... c'est plus prudent pour la phase de test...)

[DevBaldwin]

C'est normal, elle ne l'est pas

il manque l'affectation dans la requete.

Hou le vilain qui avait laissé un piège


Du coup, l'ensemble fonctionne parfaitement, il retrouve bien tous les membres du rôle donné et les retire.
Je devrais pouvoir adapter tout ça et l'intégrer dans mes scripts de gestion.

(J'ai aussi remplacé le EXEC par un PRINT... c'est plus prudent pour la phase de test...)

Idem, même si je suis sur mon environnement de Dev, j'ai des scrupules à exécuter un script que je ne connais pas

Merci à vous 2 pour votre aide.

[SQLpro]

...
Ou alors, est-ce que ce « N » sert-il à autre chose ?

Cela fait partie intégrante de la norme SQL.

• chaine ASCII => 'toto'
• chaine unicode => N'toto'

Le N signifiant NATIONAL :

• NVARCHAR = NATIONAL VARCHAR ou NATIONAL VARYING CHARACTER
• NCHAR = NATIONAL CHAR ou NATIONAL CHARACTER

Lisez mon lire pour apprendre le SQL :

Également, je crois comprendre que tu concatènes des ordres "ALTER ROLE + DROP MEMBER" (un par membre dans le rôle) dans ta variable @SQL ; que tu suffixes après avec un "DROP ROLE".
Si c'est bien cela, je ne parviens à comprendre à quel moment les "ALTER ROLE + DROP MEMBER" viennent alimenter la variable @SQL.


Saurais-tu m'éclairer ?

Un ordre SELECT est une commande qui lit ligne par ligne (dans n'importe quel ordre sauf si ORDER BY figure dans la requête) les lignes de la table. En affectant à la variable @SQL une chaine vide je l'initialise à une chaine vide pour éviter le NULL absorbant.
Ensuite, chaque chaine se concatène avec les précédent au fur et à masure de la lecture des lignes par le SELECT.

C'est pour cela que SQL Server n'a pas besoin de l'immonde GROUP_CONCAT de MySQLmerde et pratique cela depuis près de 30 ans !

A +