Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
TSQL 15
BRH 13
SQL Server 13
#SQL Server 12
#TSQL 8
#SQLServer 4
SQL Server Agent 3
Stored Procedure 2
SQL server Jobs 2
Change Data Capture CDC 2

Archive · View All
November 2010 4
October 2010 3
April 2011 3
March 2011 3
January 2012 2
November 2011 2
August 2011 2
September 2010 1
October 2011 1

Dropping A Role in SQL server using T-SQL

Mar 11 2011 7:23AM by Manjunath C Bhat   

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 sys.database_principals WHERE name = N'db_execute' AND type = 'R')
DROP ROLE [db_execute]
GO

Tags: #SQL Server, TSQL, #TSQL, SQL Server, BRH,


Manjunath C Bhat
103 · 2% · 511
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]