Dropping A Role in SQL server using T-SQL

It happens so that when you drop/delete a Database Role in SQL Server Database, the Role members who were assigned to this role wont get de-associated from this Role. No doubt the role will be dropped/deleted but the member will be having this Role still existing in their permission list. So I came up with the below T-SQL which will first de-associate all the role member’s from the role to be dropped and then it will go ahead and drop the role. For Testing Purpose, I have taken Role “db_execute” as the role which needs to be dropped/deleted.

/* De-Associating RoleMember From Role To Be Dropped*/

USE [AdventureWorks]

GO

DECLARE @RoleName sysname

set @RoleName = N'db_execute'

IF  EXISTS (SELECT * FROM dbo.sysusers WHERE name = @RoleName AND issqlrole = 1)

Begin

 

      DECLARE @RoleMemberName sysname

      /* Cursor to Loop in for Each Member have the Role Privilege and Drop RoleMember */

      DECLARE Member_Cursor CURSOR FOR

      select [name]

      from dbo.sysusers

      where uid in (

            select memberuid

            from dbo.sysmembers

            where groupuid in (

                  select uid

                  FROM dbo.sysusers where [name] = @RoleName AND issqlrole = 1))

 

      OPEN Member_Cursor;

 

      FETCH NEXT FROM Member_Cursor

      into @RoleMemberName

 

      WHILE @@FETCH_STATUS = 0

      BEGIN

 

            exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

 

            FETCH NEXT FROM Member_Cursor

            into @RoleMemberName

      END;

Read more: Beyond Relational

Posted via email from Jasper-net