Friday, March 11, 2011

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]



DECLARE @RoleName sysname

set @RoleName = N'db_execute'

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



      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





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


            FETCH NEXT FROM Member_Cursor

            into @RoleMemberName



      CLOSE Member_Cursor;

      DEALLOCATE Member_Cursor;

      /* End Of Cursor */




/* Checking If Role Name Exists In Database */

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_execute' AND type = 'R')

DROP ROLE [db_execute]


No comments:

Post a Comment