Dropping a Role from a database


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



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.