Supprimer tous les membres d'un rôle db

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 ? :P

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

Code:

---

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 +

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 :P).
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:

---

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 ?

bonjour,

Citation:

---

Envoyé par DevBaldwin

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.

Citation:

---

Envoyé par DevBaldwin

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:

---

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...)

Citation:

---

Envoyé par aieeeuuuuu

C'est normal, elle ne l'est pas ;)

il manque l'affectation dans la requete.

---

Hou le vilain qui avait laissé un piège :mrgreen:


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.

Citation:

---

Envoyé par aieeeuuuuu

(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.

Citation:

---

Envoyé par DevBaldwin

...
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 :
Pièce jointe 540225

Citation:

---

É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 +