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;

CLOSE Member_Cursor;

DEALLOCATE Member_Cursor;

/* End Of Cursor */

end

GO

/* Checking If Role Name Exists In Database */

IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = N’db_execute’ AND issqlrole = 1)

DROP ROLE [db_execute]

GO

Advertisements

One thought on “Dropping A Role in SQL server using T-SQL

  1. Appears surfing on line above 3 hours as of late, still I never identified every useful content such as your own. It really is pretty price enough personally. In my opinion, when just about all site owners as well as writers created beneficial content material since you have, the net will certainly be a great deal more practical than previously.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s