ALTER ROLE delete all members

Question

I am using SQL Server 2012, and my main goal is DROP ROLE , but this cannot be done if the role is not empty, so how can I use T-SQL to remove all members of the role first?

• I can find all members of the 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 removed 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 use this in the Entity Framework 6 migration)}}

Answer 1

This is how I would match 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 line with your ALTER ROLE for each line (user) in your request, combines them all together into one large line with all these commands, and then dynamically executes them.

Answer 2

There is a special sp ( sp_droprolemember ) in the sql server. Hope this solves your problem.