ALTER ROLE to drop all members

Question

I am using SQL Server 2012, and my main goal is to programmatically DROP ROLE, but that cannot be done unless the role is empty, so how can I use T-SQL to first drop all members of a role?

• I can find all members of a role:

  SELECT members.[name] 
  FROM sys.database_role_members AS rolemembers
      JOIN sys.database_principals AS roles 
          ON roles.[principal_id] = rolemembers.[role_principal_id]
      JOIN sys.database_principals AS members 
          ON members.[principal_id] = rolemembers.[member_principal_id]
  WHERE roles.[name]=@rolename
  

• Members can be dropped using:

  ALTER ROLE role_name DROP MEMBER user_name
  

How can I combine these two? Or is there another way to do what I'm trying to do?

_{^{(Also, I'm not sure if this is important, but I will be using this in an Entity Framework 6 migration)}}

Answer 1

Here's how I would combine the two:

DECLARE @rolename sysname = 'role_name';
DECLARE @cmd AS NVARCHAR(MAX) = N'';

SELECT @cmd = @cmd + '
    ALTER ROLE ' + QUOTENAME(@rolename) + ' DROP MEMBER ' + QUOTENAME(members.[name]) + ';'
FROM sys.database_role_members AS rolemembers
    JOIN sys.database_principals AS roles 
        ON roles.[principal_id] = rolemembers.[role_principal_id]
    JOIN sys.database_principals AS members 
        ON members.[principal_id] = rolemembers.[member_principal_id]
WHERE roles.[name]=@rolename

EXEC(@cmd);

This creates a string with your ALTER ROLE command for every row (user) in your query, concatenates them all together into one big string with all of those commands and then dynamically executes them.

Answer 2

There is special sp (sp_droprolemember) in sql server. I hope, it solves you problem.