use sybsystemprocs
go

drop procedure sp_group
go
drop procedure sp_groups
go
drop procedure sp_members
go
drop procedure sp_roles
go
drop procedure sp_sysroles
go
drop procedure sp_userroles
go

set quoted_identifier on
go

create procedure sp_group (@USER varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_group> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @dbname    varchar (30),
           @holdname  varchar (30),
           @userid    smallint

   select @holdname = isnull (@USER, user_name ())

   select @userid = user_id (@holdname)

   if (@userid IS NULL)
      begin
         select @dbname = db_name ()

         print '    USER %1! DOES NOT EXIST IN DATABASE %2!.', @holdname, @dbname

         print ' '
      end
   else if (exists (select r.lrid
                    from sysroles r
                    where r.lrid = @userid))
      begin
         print '    <%1!> IS A ROLE INSTEAD OF A USER.', @USER

         print ' '
      end
   else if (exists (select u.gid
                    from sysusers u
                    where u.uid = @userid
                      and u.gid = @userid))
      begin
         print '    <%1!> IS A GROUP INSTEAD OF A USER.', @USER

         print ' '
      end
   else
      begin
         select 'USER'  = uu.name,
                'GROUP' = ug.name
         from sysusers ug, sysusers uu
         where uu.uid = @userid
           and ug.uid = uu.gid
      end
go
sp_addcomment sp_group, 12, 'Lists the unique group of which USER (default is user) is a member'
go
sp_procxmode sp_group, anymode
go

create procedure sp_groups as
   set ansinull                      off              -- Non-ANSI
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_groups> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   create table #groups
      (uid         smallint      not null,
       grname      varchar (32)  null,
       grtype      char (1)      not null,
       memcnt      smallint      null,
       grantcnt    smallint      null,
       execcnt     smallint      null,
       selcnt      smallint      null,
       delcnt      smallint      null,
       inscnt      smallint      null,
       updcnt      smallint      null,
       refcnt      smallint      null,
       createcnt   smallint      null,
       dumpcnt     tinyint       null,
       sort        varchar (30)  null)

   insert into #groups
      select ug.uid, ug.name, 'G', count (uu.uid), 0, 0, 0, 0, 0, 0, 0, 0, 0, ug.name
      from sysusers ug,
           sysusers uu
      where ug.uid  = ug.gid                              -- group/role row
        and uu.uid <> uu.gid                              -- user row
        and ug.uid *= uu.gid                              -- map users to group
      group by ug.uid

   -- PRINT public FIRST VIA A BLANK GRTYPE.

   update #groups
   set grtype = ' '
   where grname = 'public'

   -- ELIMINATE SYSTEM ROLES WITHOUT PROTECTIONS.  NOTE THAT sysroles ALWAYS
   -- HAS ROWS FOR SYSTEM ROLES.  HOWEVER sysprotects ONLY HAS ROWS FOR
   -- "USERS" WITH PROTECTIONS.

   delete #groups
   from #groups g,
        sysroles r
   where r.lrid = g.uid
     and (not exists (select p.uid
                      from sysprotects p
                      where p.uid = g.uid))

   -- LET'S IDENTIFY ROLES AND PASSWORD PROTECTED ROLES.

   update #groups
   set grtype = 'R'
   from #groups g,
        sysroles r
   where r.lrid = g.uid

   update #groups
   set grname = grname + ' #'
   from #groups g,
        sysroles r,
        master.dbo.syssrvroles sr
   where r.lrid       = g.uid
     and sr.srid      = r.id
     and sr.password IS NOT NULL

   -- GRANTEES FOR USER ROLES HAVE TO BE COMPUTED SEPARATELY.  NOTE THAT
   -- THERE MAY BE LOGINS WITH THE ROLE WHO ARE NOT USERS IN THE DATABASE.
   -- LET'S ONLY COUNT LOGINS THAT ARE ALSO USERS.

   update #groups
   set memcnt = (select count (u.suid)
                 from sysroles r,
                      sysusers u,
                      master.dbo.sysloginroles lr
                 where r.lrid  = g.uid
                   and lr.srid = r.id
                   and u.suid  = lr.suid)
   from #groups g
   where memcnt = 0

   update #groups
   set grantcnt = (select count (p.id)
                   from sysprotects p
                   where p.uid         = g.uid
                     and p.protecttype = 1)
   from #groups g

   update #groups
   set execcnt = (select count (p.id)
                  from sysprotects p
                  where p.uid    = g.uid
                    and p.action = 224)
   from #groups g

   update #groups
   set selcnt = (select count (p.id)
                 from sysprotects p
                 where p.uid    = g.uid
                   and p.action = 193)
   from #groups g

   update #groups
   set delcnt = (select count (p.id)
                 from sysprotects p
                 where p.uid    = g.uid
                   and p.action = 196)
   from #groups g

   update #groups
   set inscnt = (select count (p.id)
                 from sysprotects p
                 where p.uid    = g.uid
                   and p.action = 195)
   from #groups g

   update #groups
   set updcnt = (select count (p.id)
                 from sysprotects p
                 where p.uid    = g.uid
                   and p.action = 197)
   from #groups g

   update #groups
   set refcnt = (select count (p.id)
                 from sysprotects p
                 where p.uid    = g.uid
                   and p.action = 151)
   from #groups g

   update #groups
   set createcnt = (select count (p.id)
                    from sysprotects p
                    where p.uid     = g.uid
                      and p.action in (198, 203, 207, 222, 233, 236))
   from #groups g

   if (not exists (select *
                   from #groups
                   where grtype = 'G'))
       begin
          print '    THERE ARE NO GROUPS OTHER THAN public.'

          print ' '
       end

   if (not exists (select *
                   from #groups
                   where grtype = 'R'))
       begin
          print '    THERE ARE NO ROLES.'

          print ' '
       end

    insert into #groups
       select          -1, NULL, grtype, NULL, NULL, NULL, NULL, NULL,
                       NULL, NULL, NULL, NULL, NULL, min (grname)
       from #groups
       where grtype in ('G', 'R')
       group by grtype
       having grtype in ('G', 'R')

   if (db_id () = 1)
      begin
         update #groups
         set dumpcnt = (select count (p.id)
                        from sysprotects p
                        where p.uid     = g.uid
                          and p.action in (228, 235))
         from #groups g
         where g.uid <> -1

         select 'GRP/ROLE' = isnull (substring (grname, 1, 8), ' '),
                'MEMBERS'  = isnull (substring (convert (char (10), convert (money, memcnt), 1), 1, 7), ' '),
                'GRANTS'   = isnull (substring (convert (char (9), convert (money, grantcnt), 1), 1, 6), ' '),
                'EXEC'     = isnull (substring (convert (char (9), convert (money, execcnt), 1), 1, 6), ' '),
                'SELECT'   = isnull (substring (convert (char (9), convert (money, selcnt), 1), 1, 6), ' '),
                'DELETE'   = isnull (substring (convert (char (9), convert (money, delcnt), 1), 1, 6), ' '),
                'INSERT'   = isnull (substring (convert (char (9), convert (money, inscnt), 1), 1, 6), ' '),
                'UPDATE'   = isnull (substring (convert (char (9), convert (money, updcnt), 1), 1, 6), ' '),
                'REF'      = isnull (substring (convert (char (9), convert (money, refcnt), 1), 1, 6), ' '),
                'CREATE'   = isnull (substring (convert (char (8), convert (money, createcnt), 1), 1, 5), ' '),
                'DUMP'     = isnull (substring (convert (char (7), convert (money, dumpcnt), 1), 1, 4), ' ')
         from #groups
         order by grtype, sort
      end
   else
      begin
         select 'GROUP/ROLE' = isnull (substring (grname, 1, 14), ' '),
                'MEMBERS'    = isnull (substring (convert (char (10), convert (money, memcnt), 1), 1, 7), ' '),
                'GRANTS'     = isnull (substring (convert (char (9), convert (money, grantcnt), 1), 1, 6), ' '),
                'EXEC'       = isnull (substring (convert (char (9), convert (money, execcnt), 1), 1, 6), ' '),
                'SELECT'     = isnull (substring (convert (char (9), convert (money, selcnt), 1), 1, 6), ' '),
                'DELETE'     = isnull (substring (convert (char (9), convert (money, delcnt), 1), 1, 6), ' '),
                'INSERT'     = isnull (substring (convert (char (9), convert (money, inscnt), 1), 1, 6), ' '),
                'UPDATE'     = isnull (substring (convert (char (9), convert (money, updcnt), 1), 1, 6), ' '),
                'REF'        = isnull (substring (convert (char (8), convert (money, refcnt), 1), 1, 5), ' '),
                'CREATE'     = isnull (substring (convert (char (9), convert (money, createcnt), 1), 1, 6), ' ')
         from #groups
         order by grtype, sort
      end

   print ' '
go
sp_addcomment sp_groups, 12, 'Lists all groups and local roles with aggregate data'
go
sp_procxmode sp_groups, anymode
go

create procedure sp_members (@GROUP_ROLE varchar (30) = NULL) as
   set ansinull                      on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_members> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare  @dbname    varchar (30),
            @maxgrlen  tinyint

   create table #members
      (type    char (1)      not null,              -- "G" or "R"
       grname  varchar (32)  not null,              -- Group or Role name
       member  varchar (30)  not null,              -- member name
       sort    varchar (63)  not null)

   -- INSERT ALL OF THE GROUPS

   insert into #members
      select 'G', ug.name, uu.name, ug.name + ' ' + uu.name
      from sysusers ug, sysusers uu
      where ug.name = isnull (@GROUP_ROLE, ug.name)
        and ug.uid <> 0                              -- eliminate "public"
        and ug.uid  = ug.gid                         -- group/role row
        and uu.gid  = ug.uid                         -- map user to grnameoup
        and uu.uid <> uu.gid                         -- user row

   insert into #members
      select 'G', ug.name, '(none)', ug.name
      from sysusers ug
      where @GROUP_ROLE IS NULL
        and ug.uid       = ug.gid                   -- group/role row
        and not exists (select r.lrid               -- not a role
                        from sysroles r
                        where r.lrid = ug.gid)
        and not exists (select uu.gid               -- no members
                        from sysusers uu
                        where uu.gid  = ug.uid
                          and uu.uid <> uu.gid)

   -- INSERT ALL OF THE "LOCAL" ROLES

   insert into #members
      select 'R', sr.name, u.name, sr.name + ' ' + u.name
      from sysroles r,
           sysusers u,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.name      = isnull (@GROUP_ROLE, sr.name)
        and sr.password IS NULL                     -- no password
        and r.id         = sr.srid                  -- only roles locally referred to
        and lr.srid      = sr.srid
        and u.suid       = lr.suid

   insert into #members
      select 'R', sr.name + ' #', u.name, sr.name + ' ' + u.name
      from sysroles r,
           sysusers u,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.name      = isnull (@GROUP_ROLE, sr.name)
        and sr.password IS NOT NULL                 -- password
        and r.id         = sr.srid                  -- only roles locally referred to
        and lr.srid      = sr.srid
        and u.suid       = lr.suid

   insert into #members
      select 'R', sr.name, '(none)', sr.name
      from sysroles r,
           master.dbo.syssrvroles sr
      where sr.name      = isnull (@GROUP_ROLE, sr.name)
        and sr.password IS NULL                     -- no password
        and r.id         = sr.srid                  -- only roles locally referred to
        and not exists (select m.*                  -- no assigned logins
                        from #members m
                        where m.grname = sr.name)

   insert into #members
      select 'R', sr.name + ' #', '(none)', sr.name
      from sysroles r,
           master.dbo.syssrvroles sr
      where sr.name      = isnull (@GROUP_ROLE, sr.name)
        and sr.password IS NOT NULL                 -- password
        and r.id         = sr.srid                  -- only roles locally referred to
        and not exists (select m.*                  -- no assigned logins
                        from #members m
                        where m.grname = sr.name  +  ' #')

   -- PRETTYPRINT THE INFORMATION

   if (exists (select *
               from #members))
      begin
         update #members
         set grname = ' '
         from #members m
         where exists (select m2.grname
                       from #members m2
                       where m2.grname = m.grname
                         and m2.member = m.member
                         and m2.member > (select min (m3.member)
                                          from #members m3
                                          where m3.grname = m2.grname))

         if ((@GROUP_ROLE IS NULL)  and
             (exists (select *
                      from #members
                      where type = 'G')))
            begin
               insert into #members
                  select 'R', ' ', ' ', min (sort)
                  from #members
                  where type = 'R'
            end

         select @maxgrlen = max (char_length (grname))
         from #members

         if (exists (select *
                     from #members
                     where charindex ('#', grname) > 0))
            begin
               if (@maxgrlen < 26)
                  begin
                     select 'GROUP/ROLE (# = password)' = substring (grname, 1, 25),
                            'MEMBER/GRANTEE'            = member
                     from #members
                     order by type, sort, grname
                  end
               else
                  begin
                     select 'GROUP/ROLE (# = password)' = grname,
                            'MEMBER/GRANTEE'            = member
                     from #members
                     order by type, sort, grname
                  end
            end
         else
            begin
               if (@maxgrlen < 20)
                  begin
                     select 'GROUP/ROLE'     = substring (grname, 1, 19),
                            'MEMBER/GRANTEE' = member
                     from #members
                     order by type, sort, grname
                  end
               else
                  begin
                     select 'GROUP/ROLE'     = substring (grname, 1, 30),
                            'MEMBER/GRANTEE' = member
                     from #members
                     order by type, sort, grname
                  end
            end
      end
   else if (@GROUP_ROLE IS NULL)
      begin
         select @dbname = db_name ()

         print '    THERE ARE NO GROUPS OR REFERENCED ROLES IN DATABASE%1!.',
               @dbname
      end
   else
      begin
         select @dbname = db_name ()

         print '    THERE IS NO GROUP OR REFERENCED ROLE <%1!> IN DATABASE %2!.',
               @GROUP_ROLE, @dbname
      end

   print ' '
go
sp_addcomment sp_members, 12, 'Lists all members of a GROUP/ROLE or (default) all groups and roles'
go
sp_procxmode sp_members, anymode
go

create procedure sp_roles as

   execute sp_groups

go
sp_addcomment sp_roles, 12, 'Lists all local roles and groups with aggregate data'
go
sp_procxmode sp_roles, anymode
go

create procedure sp_sysroles (@ROLE varchar (30) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_sysroles> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @attribid     smallint,
           @attribclass  smallint

   create table #sysroles
     (rolename     varchar (30)  not null,
      loginname    varchar (30)  not null,
      sort         varchar (48)  not null)

   create table #directrole
     (rolename     varchar (30)  not null,
      subrolename  varchar (30)  not null)

   select @attribid    = attribute,
          @attribclass = class
   from master.dbo.sysattributes
   where object_type = 'UR'

   insert into #directrole
      select role_name (a.object),
             role_name (a.object_info1)
      from master.dbo.sysattributes a,
           master.dbo.syssrvroles   sr
      where sr.srid     < 32
        and sr.name     = role_name (a.object_info1)
        and a.class     = @attribclass
        and a.attribute = @attribid

   while (exists (select a.attribute
                  from #directrole r,
                       master.dbo.sysattributes a
                  where r.rolename  = role_name (a.object_info1)
                    and a.class     = @attribclass
                    and a.attribute = @attribid
                    and not exists (select r2.*
                                    from #directrole r2
                                    where r2.rolename    = role_name (a.object)
                                      and r2.subrolename = r.subrolename)))
      begin
         insert into #directrole
            select role_name (a.object),
                   r.subrolename
            from #directrole r,
                 master.dbo.sysattributes a
            where r.rolename  = role_name (a.object_info1)
              and a.class     = @attribclass
              and a.attribute = @attribid
              and not exists (select r2.*
                              from #directrole r2
                              where r2.rolename    = role_name (a.object)
                                and r2.subrolename = r.subrolename)
      end

   if (exists (select *
               from #directrole
               where subrolename = isnull (@ROLE, subrolename)))
      begin
         insert into #sysroles
            select r.subrolename,
                   ltrim (substring (isnull (suser_name (lr.suid), '(none)') + ' (' + r.rolename + ')', 1, 30)),
                   r.subrolename  +  ' '  +  ltrim (substring (isnull (suser_name (lr.suid), '(none)') + ' (' + r.rolename + ')', 1, 48))
            from master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr,
                 #directrole r
            where r.subrolename  = isnull (@ROLE, r.subrolename)
              and sr.name        = r.rolename
              and sr.srid       *= lr.srid

         insert into #sysroles
            select sr.name,
                   isnull (suser_name (lr.suid), '(none)'),
                   sr.name  +  ' '  +  isnull (suser_name (lr.suid), '(none)')
            from master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr
            where sr.name  = isnull (@ROLE, sr.name)
              and sr.srid  < 32
              and sr.srid *= lr.srid

          update #sysroles
          set rolename = ' '
          from #sysroles s1
          where exists (select *
                        from #sysroles s2
                        where s2.rolename  = s1.rolename
                          and s2.loginname = s1.loginname
                          and s2.loginname > (select min (loginname)
                                              from #sysroles s3
                                              where s3.rolename = s2.rolename))

         select distinct 'SYSTEM ROLE'          = substring (rolename, 1, 17),
                         'LOGIN (PARENT ROLES)' = loginname
         from #sysroles
         order by sort
      end
   else if (exists (select lr.srid
                    from master.dbo.sysloginroles lr,
                         master.dbo.syssrvroles sr
                    where sr.name  = isnull (@ROLE, sr.name)
                      and sr.srid  < 32
                      and sr.srid *= lr.srid))
      begin
         insert into #sysroles
            select sr.name,
                   isnull (suser_name (lr.suid), '(none)'),
                   sr.name  +  ' '  +  isnull (suser_name (lr.suid), '(none)')
            from master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr
            where sr.name  = isnull (@ROLE, sr.name)
              and sr.srid  < 32
              and sr.srid *= lr.srid

         update #sysroles
         set rolename = ' '
         from #sysroles s1
         where exists (select *
                       from #sysroles s2
                       where s2.rolename  = s1.rolename
                         and s2.loginname = s1.loginname
                         and s2.loginname > (select min (loginname)
                                             from #sysroles s3
                                             where s3.rolename = s2.rolename))

         select 'SYSTEM ROLE' = substring (rolename, 1, 17),
                'LOGIN'       = loginname
         from #sysroles
         order by sort
      end
   else if (exists (select lr.srid
                    from master.dbo.sysloginroles lr,
                         master.dbo.syssrvroles sr,
                         #directrole r
                    where suser_name (lr.suid) = @ROLE
                      and sr.srid              = lr.srid
                      and sr.name              = r.rolename))
      begin
         insert into #sysroles
            select substring (r.subrolename  +  ' (' + r.rolename + ')', 1, 30), ' ', ' '
            from master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr,
                 #directrole r
            where suser_name (lr.suid) = @ROLE
              and sr.srid              = lr.srid
              and sr.name              = r.rolename

         insert into #sysroles
            select sr.name, ' ', ' '
            from master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr
            where suser_name (lr.suid) = @ROLE
              and sr.srid              = lr.srid
              and sr.srid              < 32

         select distinct 'SYSTEM ROLE (PARENT ROLES)' = rolename
         from #sysroles
         order by rolename
      end
   else if (exists (select lr.srid
                    from master.dbo.sysloginroles lr,
                         master.dbo.syssrvroles sr
                    where suser_name (lr.suid) = @ROLE
                      and sr.srid              = lr.srid
                      and sr.srid              < 32))
      begin
         select 'SYSTEM ROLE' = sr.name
         from master.dbo.sysloginroles lr,
              master.dbo.syssrvroles sr
         where suser_name (lr.suid) = @ROLE
           and sr.srid              = lr.srid
           and sr.srid              < 32
         order by 'SYSTEM ROLE'
      end
   else
      begin
         print '    <%1!> IS NEITHER A SYSTEM ROLE NOR A LOGIN WITH A SYSTEM ROLE.',
               @ROLE

         print ' '
      end
go
sp_addcomment sp_sysroles, 12, 'Lists all system roles with various scope options:'
go
sp_addcomment sp_sysroles, 12, '   (NULL):      all system roles with their logins (default)'
go
sp_addcomment sp_sysroles, 12, '   LOGIN:       all system roles for LOGIN'
go
sp_addcomment sp_sysroles, 12, '   SYSTEM ROLE: all logins with SYSTEM ROLE role'
go
sp_procxmode sp_sysroles, anymode
go

create procedure sp_userroles (@ROLE varchar (30) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_userroles> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @attribid     smallint,
           @attribclass  smallint,
           @maxlgnlen    tinyint,
           @maxurlen     tinyint

   create table #userroles
     (name    varchar (30)  not null,
      srid    smallint      not null,
      top     char (3)      not null,
      pw      char (3)      not null,
      logins  smallint      not null,
      dflts   smallint      not null)

   create table #userroleslogins
     (urole   varchar (30)  not null,
      lgn     varchar (30)  not null,
      status  smallint      not null,
      sort    varchar (61)  not null)

   insert into #userroles
      select name, srid, 'Yes', isnull (substring (password, 1, 3), 'No'), 0, 0
      from master.dbo.syssrvroles
      where name = isnull (@ROLE, name)
        and srid > 31

   if (exists (select *
               from #userroles))
      begin
         update #userroles
         set dflts = (select count (lr.srid)
                      from master.dbo.sysloginroles lr
                      where lr.srid   = u.srid
                        and lr.status = 1)
         from #userroles u

         select @attribid    = attribute,
                @attribclass = class
         from master.dbo.sysattributes
         where object_type = 'UR'

         update #userroles
         set top  = 'No'
         from #userroles u
         where exists (select a.*
                       from master.dbo.sysattributes a
                       where a.class     = @attribclass
                         and a.attribute = @attribid
                         and u.name      = role_name (a.object_info1))

         update #userroles
         set pw = 'Yes'
         where pw <> 'No'

         update #userroles
         set logins = (select count (lr.srid)
                       from master.dbo.sysloginroles lr,
                            master.dbo.syssrvroles sr
                       where sr.name = r.name
                         and lr.srid = sr.srid)
         from #userroles r

         select @maxurlen = max (char_length (name))
         from #userroles

         if (@maxurlen < 15)
            begin
               print '                                            ACTIVE'

               select 'USER ROLE' = substring (name, 1, 14),
                      'TOP ROLE?' = top,
                      'PASSWORD?' = pw,
                      LOGINS      = substring (convert (char (9), convert (money, logins), 1), 1, 6),
                      'DEFAULTS'  = substring (convert (char (11), convert (money, dflts), 1), 1, 8)
               from #userroles
               order by 'USER ROLE'
            end
         else if (@maxurlen < 21)
            begin
               print '                                                  ACTIVE'

               select 'USER ROLE' = substring (name, 1, 20),
                      'TOP ROLE?' = top,
                      'PASSWORD?' = pw,
                      LOGINS      = substring (convert (char (9), convert (money, logins), 1), 1, 6),
                      'DEFAULTS'  = substring (convert (char (11), convert (money, dflts), 1), 1, 8)
               from #userroles
               order by 'USER ROLE'
            end
         else
            begin
               print '                                                            ACTIVE'

               select 'USER ROLE' = name,
                      'TOP ROLE?' = top,
                      'PASSWORD?' = pw,
                      LOGINS      = substring (convert (char (9), convert (money, logins), 1), 1, 6),
                      'DEFAULTS'  = substring (convert (char (11), convert (money, dflts), 1), 1, 8)
               from #userroles
               order by 'USER ROLE'
            end

         if (exists (select *
                     from #userroles
                     where logins > 0))
            begin
               print ' '

               insert into #userroleslogins
                  select ur.name, l.name , lr.status, ur.name + ' ' + l.name
                  from #userroles ur,
                       master.dbo.syslogins l,
                       master.dbo.sysloginroles lr
                  where lr.srid = ur.srid
                    and l.suid  = lr.suid

               update #userroleslogins
               set urole = ' '
               from #userroleslogins url
               where exists (select url2.urole
                             from #userroleslogins url2
                             where url2.urole = url.urole
                               and url2.lgn   = url.lgn
                               and url2.lgn   > (select min (url3.lgn)
                                                 from #userroleslogins url3
                                                 where url3.urole = url2.urole))

               select @maxlgnlen = max (char_length (lgn))
               from #userroleslogins

               if ((@maxurlen < 15)  and  (@maxlgnlen < 15))
                  begin
                     select 'USER ROLE'  = substring (ur.urole, 1, 14),
                            'LOGIN NAME' = substring (ur.lgn, 1, 14),
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if ((@maxurlen < 15)  and  (@maxlgnlen < 21))
                  begin
                     select 'USER ROLE'  = substring (ur.urole, 1, 14),
                            'LOGIN NAME' = substring (ur.lgn, 1, 20),
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if (@maxurlen < 15)
                  begin
                     select 'USER ROLE'  = substring (ur.urole, 1, 14),
                            'LOGIN NAME' = ur.lgn,
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if ((@maxurlen < 21)  and  (@maxlgnlen < 15))
                  begin
                     select 'USER ROLE'  = substring (ur.urole, 1, 20),
                            'LOGIN NAME' = substring (ur.lgn, 1, 14),
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if ((@maxurlen < 21)  and  (@maxlgnlen < 21))
                  begin
                     select 'USER ROLE'  = substring (ur.urole, 1, 20),
                            'LOGIN NAME' = substring (ur.lgn, 1, 20),
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if (@maxurlen < 21)
                  begin
                     select 'USER ROLE'  = substring (ur.urole, 1, 20),
                            'LOGIN NAME' = ur.lgn,
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if (@maxlgnlen < 15)
                  begin
                     select 'USER ROLE'  = ur.urole,
                            'LOGIN NAME' = substring (ur.lgn, 1, 15),
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else if (@maxlgnlen < 21)
                  begin
                     select 'USER ROLE'  = ur.urole,
                            'LOGIN NAME' = substring (ur.lgn, 1, 20),
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
               else
                  begin
                     select 'USER ROLE'  = ur.urole,
                            'LOGIN NAME' = ur.lgn,
                            'DEFAULT?'   = substring (vB.name, 1, 8)
                     from #userroleslogins ur,
                          sybsystemprocs.dbo.usptvalues vB
                     where vB.type   = 'B'
                       and vB.number = ur.status
                     order by sort
                  end
            end
      end
   else if (@ROLE IS NULL)
      begin
         print '    THERE ARE NO USER ROLES.'

         print ' '
      end
   else
      begin
         print '    USER ROLE <%1!> DOES NOT EXIST.', @ROLE

         print ' '
      end
go
sp_addcomment sp_userroles, 12, 'Lists info for all user roles (default) or for USER ROLE'
go
sp_procxmode sp_userroles, anymode
go

dump transaction sybsystemprocs with truncate_only
go
