Advanced Search

Dropping a Role from a database

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. USE [DATABASE name]
  2. GO
  3.  
  4. DECLARE @RoleName sysname
  5. SET @RoleName = N'role name'
  6.  
  7. BEGIN
  8.  
  9.       DECLARE @RoleMemberName sysname
  10.       DECLARE Member_Cursor CURSOR FOR
  11.       SELECT [name]
  12.       FROM dbo . sysusers
  13.        WHERE uid IN (
  14.              SELECT memberuid
  15.             FROM dbo . sysmembers
  16.             WHERE groupuid IN (
  17.                   SELECT uid
  18.                   FROM dbo . sysusers WHERE [name] = @RoleName AND issqlrole = 1 ))
  19.  
  20.       OPEN Member_Cursor ;
  21.  
  22.       FETCH NEXT FROM Member_Cursor
  23.       INTO @RoleMemberName
  24.  
  25.       WHILE @@FETCH_STATUS = 0
  26.       BEGIN
  27.  
  28.             EXEC sp_droprolemember @rolename = @RoleName , @membername = @RoleMemberName
  29.  
  30.             FETCH NEXT FROM Member_Cursor
  31.             INTO @RoleMemberName
  32.       END ;
  33.  
  34.       CLOSE Member_Cursor ;
  35.       DEALLOCATE Member_Cursor ;
  36.  
  37. END
  38.  
  39. GO
  40. DROP ROLE [rolename]
  41. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.