use sybsystemprocs
go

/*
 * DROP PROC sp__revroles
 */
IF OBJECT_ID('sp__revroles') IS NOT NULL
BEGIN
    DROP PROC sp__revroles
    PRINT '<<< Dropped proc sp__revroles >>>'
END
go
create procedure sp__revroles
as
/* Created 03/05/97 by Clayton Groom
creates a reverse engineered set of commands to restore user roles
*/
select "exec sp_role grant, " + u.name + ", " + s.name + char(13) + char(10) + "go"
from    master..syssrvroles s,
        sysroles r,
        sysusers u
where   r.id    = s.srid
and     r.lrid  = u.uid
and     s.name <> u.name
go

IF OBJECT_ID('sp__revroles') IS NOT NULL
    PRINT '<<< Created proc sp__revroles >>>'
ELSE
    PRINT '<<< Failed to create proc sp__revroles >>>'
go
