Please use FireFox to view this page

create procedure sp_dropapprole @rolename sysname -- role to be dropped as -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @uid smallint, @ret int -- CHECK PERMISSIONS -- if (not is_member('db_securityadmin') = 1) and (not is_member('db_owner') = 1) begin raiserror(15247,-1,-1) return (1) end -- DISALLOW USER TRANSACTION -- set implicit_transactions off if (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_dropapprole') return (1) end -- ERROR IF ROLE NOT FOUND -- select @uid = uid from sysusers where name = @rolename and isapprole = 1 if @uid is null begin raiserror(15014,-1,-1,@rolename) return (1) end -- CHECK IF ROLE OWNS ANYTHING -- execute @ret = sp_MScheck_uid_owns_anything @uid if @ret <> 0 return (1) -- DROP SYSUSERS AND PROTECTION ENTRIES -- delete from syspermissions where grantee = @uid delete from sysusers where uid = @uid -- FINALIZATION: PRINT/RETURN SUCCESS -- if @@error <> 0 return (1) raiserror(15495,-1,-1) return (0) -- sp_dropapprole