use sybsystemprocs
go

sp_dropprocedure sp_groups
go
sp_dropprocedure sp_members
go
sp_dropprocedure sp_roles
go
sp_dropprocedure sp_sysroles
go
sp_dropprocedure sp_userroles
go

set quoted_identifier on
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', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ug.name
      from sysusers ug
      where ug.uid  = ug.gid                              -- group/role row
        and not exists (select uu.uid
                        from sysusers uu
                        where uu.gid  = ug.uid            -- map users to group
                          and uu.uid <> uu.gid)           -- user row

   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 ug.uid  = uu.gid                              -- map users to group
        and uu.uid <> uu.gid                              -- user row
      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 + ' (pwd)'
   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),
       execcnt   = (select count (p.id)
                    from sysprotects p
                    where p.uid    = g.uid
                      and p.action = 224),
       selcnt    = (select count (p.id)
                    from sysprotects p
                    where p.uid    = g.uid
                      and p.action = 193),
       delcnt    = (select count (p.id)
                    from sysprotects p
                    where p.uid    = g.uid
                      and p.action = 196),
       inscnt    = (select count (p.id)
                    from sysprotects p
                    where p.uid    = g.uid
                      and p.action = 195),
       updcnt    = (select count (p.id)
                    from sysprotects p
                    where p.uid    = g.uid
                      and p.action = 197),
       refcnt    = (select count (p.id)
                    from sysprotects p
                    where p.uid    = g.uid
                      and p.action = 151),
       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, '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 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_members CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring  varchar (255),
           @dbname     varchar ( 30),
           @grlen      tinyint,
           @grminlen   tinyint,
           @grname     varchar ( 30),
           @grtitle    varchar ( 10),
           @len2       tinyint,
           @len3       tinyint,
           @maxseq     tinyint,
           @memlen     tinyint,
           @memtitle   varchar ( 14),
           @seq2       tinyint,
           @seq3       tinyint,
           @uid        smallint,
           @username   varchar ( 30)

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

   create table #members2
      (type    char (1)        not null,
       grname  varchar (30)    not null,
       member  varchar (30)    not null,
       seq     numeric (7, 0)  identity)

   create table #members3
      (type    char (1)        not null,
       grname  varchar (30)    not null,
       memtext varchar (75)    not null,
       sort    varchar (30)    not null,
       seq     int             not null)

   if (@GROUP_ROLE = 'public')
      begin
         insert into #members1
            select 'G', 'public', name, uid, name
            from sysusers
            where uid <> 0
              and gid  = 0
      end
   else
      begin
         -- INSERT ALL OF THE GROUPS

         insert into #members1
            select 'G', ug.name, uu.name, uu.uid, ug.name + ' ' + uu.name
            from sysusers ug, sysusers uu
            where ug.name = coalesce (@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 #members1
            select 'G', ug.name, '-', 0, 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 #members1
            select 'R', sr.name, u.name, u.uid, sr.name + ' ' + u.name
            from sysroles r,
                 sysusers u,
                 master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr
            where sr.name      = coalesce (@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 #members1
            select 'R', sr.name + ' #', u.name, u.uid, sr.name + ' ' + u.name
            from sysroles r,
                 sysusers u,
                 master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr
            where sr.name      = coalesce (@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
      end

   -- DISPLAY ONLY THE "APPROPRIATE" ROLE INFORMATION

   select @dbname   = db_name (),
          @uid      = user_id (),
          @username = user_name ()

   if ((charindex ('sso_role', show_role ()) = 0)  and
       (charindex ('sa_role', show_role ()) = 0)   and
       (@uid <> 1))
      begin
         delete from #members1
         where type    = 'R'
           and memuid <> @uid

         if (exists (select *
                     from #members1
                     where type = 'R'))
            begin
               print '    ROLE INFORMATION ONLY FOR %1! WILL BE DISPLAYED.',
                     @username

               print ' '
            end
      end

   -- PRETTYPRINT THE INFORMATION

   if (exists (select *
               from #members1))
      begin
         select @grlen = max (char_length (grname))
         from #members1

         if ((exists (select *
                      from #members1
                      where type = 'G'))  and
             (exists (select *
                      from #members1
                      where type = 'R')))
            begin
               select @grtitle  = 'GROUP/ROLE',
                      @memtitle = 'MEMBER/GRANTEE',
                      @grminlen = 10
            end
         else if (exists (select *
                          from #members1
                          where type = 'G'))
            begin
               print '    THERE ARE NO APPROPRIATE REFERENCED ROLES IN %1!.',
                     @dbname

               print ' '

               select @grtitle  = 'GROUP',
                      @memtitle = 'MEMBER',
                      @grminlen = 5
            end
         else
            begin
               print '    THERE ARE NO GROUPS IN %1!.', @dbname

               print ' '

               select @grtitle  = 'ROLE',
                      @memtitle = 'GRANTEE',
                      @grminlen = 4
            end

         if (@grlen < @grminlen)
            begin
               select @grlen = @grminlen
            end

         select @grname = min (grname),
                @memlen = 76 - @grlen,
                @seq3   = 1
         from #members1

         insert into #members2
            select type, grname, member
            from #members1
            order by type, grname, sort

         while (@grname IS NOT NULL)
            begin
               select @maxseq = convert (tinyint, max (seq)),
                      @seq2   = convert (tinyint, min (seq))
               from #members2
               where grname = @grname

               while (@seq2 <= @maxseq)
                  begin
                     if (exists (select *
                                 from #members3
                                 where sort = @grname))
                        begin
                           select @len2 = char_length (member)
                           from #members2
                           where seq = @seq2

                           select @len3 = char_length (memtext)
                           from #members3
                           where seq  = @seq3
                             and sort = @grname

                           if ((@len2 + @len3 + 2) > @memlen)
                              begin
                                 update #members3
                                 set memtext = memtext  +  ','
                                 where seq  = @seq3
                                   and sort = @grname

                                 select @seq3 = @seq3 + 1

                                 insert into #members3
                                    select type, ' ', member, grname, @seq3
                                    from #members2
                                    where seq    = @seq2
                                      and grname = @grname
                              end
                           else
                              begin
                                 update #members3
                                 set memtext = s3.memtext  +  ', '  +  s2.member
                                 from #members2 s2, #members3 s3
                                 where s3.seq  = @seq3
                                   and s3.sort = @grname
                                   and s2.seq  = @seq2
                              end

                           select @seq2 = @seq2 + 1
                        end
                     else
                        begin
                           insert into #members3
                              select type, grname, member, grname, @seq3
                              from #members2
                              where seq = @seq2

                           select @seq2 = @seq2 + 1
                        end
                  end

               select @grname = min (grname)
               from #members2
               where grname > @grname
            end

         if @grtitle = 'GROUP/ROLE'     -- VISUALLY SEPERATE GROUPS AND ROLES
            begin
               insert into #members3
                  select 'R', ' ', ' ', min (sort), 0
                  from #members3
                  where type = 'R'
            end

         select @memlen = max (char_length (memtext))
         from #members3

         select @cmdstring = 'select '''
                             +  @grtitle
                             +  ''' = substring (grname, 1,'
                             +  convert (varchar (2), @grlen)
                             +  '),'''
                             +  @memtitle
                             +  ''' = substring (memtext, 1,'
                             +  convert (varchar (2), @memlen)
                             +  ')'
                             +  ' from #members3'
                             +  ' order by type, sort, seq'

         execute  (@cmdstring)
      end
   else if (@GROUP_ROLE IS NULL)
      begin
         print '    THERE ARE NO GROUPS OR REFERENCED ROLES IN %1!.', @dbname
      end
   else
      begin
         print '    THERE IS NO GROUP OR APPROPRIATE REFERENCED ROLE %1! IN %2!.',
               @GROUP_ROLE, @dbname
      end

   print ' '
go
sp_addcomment sp_members, 'Lists members of groups and local roles'
go
sp_procxmode sp_members, anymode
go

create procedure sp_roles as

   execute  sp_groups

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

create procedure sp_sysroles 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_sysroles CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if ((charindex ('sa_role', show_role ()) = 0)  and  (charindex ('sso_role', show_role ()) = 0))
      begin
         print '    ONLY AN ADMINISTRATOR OR SECURITY OFFICER CAN EXECUTE sp_sysroles.'

         print ' '

         return 1
      end

   declare @attribid     smallint,
           @attribclass  smallint,
           @cmdstring    varchar (255),
           @len2         tinyint,
           @len3         tinyint,
           @loginlen     tinyint,
           @maxseq       tinyint,
           @maxsrid      smallint,
           @melen        tinyint,
           @rolelen      tinyint,
           @rolename     varchar ( 30),
           @seq2         tinyint,
           @seq3         tinyint,
           @seq          tinyint

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

   create table #sysroles2
     (rolename     varchar ( 30)   not null,
      loginname    varchar ( 30)   not null,
      seq          numeric (7, 0)  identity)

   create table #sysroles3
     (rolename     varchar ( 30)   not null,
      logintext    varchar ( 70)   not null,
      sort         varchar ( 30)   not null,
      seq          tinyint         not null)

   create table #sysroles4
     (rolename     varchar ( 30)   not null,
      mutexcl      varchar (255)   null)

   create table #sysroles5
     (rolename     varchar ( 30)   not null,
      mutexcl      varchar (255)   null,
      sort         varchar ( 30)   not null,
      seq          tinyint         not null)

   -- ACQUIRE ALL USER ROLES THAT ARE GRANTED A SYSTEM ROLE

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

   insert into #sysroles1
      select role_name (a.object_info1),
             role_name (a.object),
             role_name (a.object)
      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 #sysroles1 r,
                       master.dbo.sysattributes a
                  where r.loginname = role_name (a.object_info1)
                    and a.class     = @attribclass
                    and a.attribute = @attribid
                    and not exists (select r2.*
                                    from #sysroles1 r2
                                    where r2.loginname = role_name (a.object)
                                      and r2.rolename  = r.rolename)))
      begin
         insert into #sysroles1
            select r.rolename, role_name (a.object), role_name (a.object)
            from #sysroles1 r,
                 master.dbo.sysattributes a
            where r.loginname = role_name (a.object_info1)
              and a.class     = @attribclass
              and a.attribute = @attribid
              and not exists (select r2.*
                              from #sysroles1 r2
                              where r2.loginname = role_name (a.object)
                                and r2.rolename  = r.rolename)
      end

   update #sysroles1
   set loginname = '('  +  loginname  +  ')'

   -- ACQUIRE ALL SYSTEM ROLES WITH THEIR LOGINS

   insert into #sysroles1
      select sr.name, isnull (suser_name (lr.suid), '-'), isnull (suser_name (lr.suid), '-')
      from master.dbo.syssrvroles sr  left join  master.dbo.sysloginroles lr
        on sr.srid = lr.srid
      where sr.srid < 32

   delete #sysroles1
   from #sysroles1 s1
   where s1.loginname = '-'
     and exists (select sl.rolename
                 from #sysroles1 sl
                 where sl.rolename   = s1.rolename
                   and sl.loginname <> '-')

   -- LOAD #sysroles2 WITH AN IDENTITY CORRESPONDING TO DESIRED SORT

   select @rolelen = max (char_length (rolename))
   from #sysroles1

   insert into #sysroles2
      select rolename, loginname
      from #sysroles1
      order by rolename, sort

   -- LOAD #sysroles3 WITH FORMATTED USER ROLES AND LOGINS

   select @rolename = min (rolename),
          @loginlen = 76 - @rolelen,
          @seq3     = 1
   from #sysroles2

   while (@rolename IS NOT NULL)
      begin
         select @maxseq = convert (tinyint, max (seq)),
                @seq2   = convert (tinyint, min (seq))
         from #sysroles2
         where rolename = @rolename

         while (@seq2 <= @maxseq)
            begin
               if (exists (select *
                           from #sysroles3
                           where sort = @rolename))
                  begin
                     select @len2 = char_length (loginname)
                     from #sysroles2
                     where seq = @seq2

                     select @len3 = char_length (logintext)
                     from #sysroles3
                     where seq  = @seq3
                       and sort = @rolename

                     if ((@len2 + @len3 + 2) > @loginlen)
                        begin
                           update #sysroles3
                           set logintext = logintext  +  ','
                           where seq  = @seq3
                             and sort = @rolename

                           select @seq3 = @seq3 + 1

                           insert into #sysroles3
                              select ' ', loginname, rolename, @seq3
                              from #sysroles2
                              where seq      = @seq2
                                and rolename = @rolename
                        end
                     else
                        begin
                           update #sysroles3
                           set logintext = s3.logintext  +  ', '  +  s2.loginname
                           from #sysroles2 s2, #sysroles3 s3
                           where s3.seq  = @seq3
                             and s3.sort = @rolename
                             and s2.seq  = @seq2
                        end

                     select @seq2 = @seq2 + 1
                  end
               else
                  begin
                     insert into #sysroles3
                        select rolename, loginname, rolename, @seq3
                        from #sysroles2
                        where seq = @seq2

                     select @seq2 = @seq2 + 1
                  end
            end

         select @rolename = min (rolename)
         from #sysroles2
         where rolename > @rolename
      end

   select @cmdstring = 'select ''SYSTEM ROLE'' = substring (rolename, 1,'
                       +  convert (varchar (2), @rolelen)
                       +  '),'

   if (exists (select *
               from #sysroles3
               where charindex ('(', logintext) > 0))
      begin
         select @cmdstring = @cmdstring
                             +  '''LOGIN / (USER ROLE)'' = substring (logintext, 1,'
      end
   else
      begin
         select @cmdstring = @cmdstring
                             +  '''LOGIN'' = substring (logintext, 1,'
      end

   select @cmdstring = @cmdstring
                       +  convert (varchar (2), @loginlen)
                       +  ')'
                       +  ' from #sysroles3'
                       +  ' order by sort, seq'

   execute  (@cmdstring)

   print ' '

   -- LOAD #sysroles4 WITH MUTUAL EXCLUSIVITY DATA

   insert into #sysroles4
      select distinct rolename, 'X'
      from #sysroles1

   select @maxsrid = max (srid),
          @seq     = 1
   from master.dbo.syssrvroles

   while (@seq <= @maxsrid)
      begin
         select @rolename = role_name (@seq)

         if (@rolename IS NOT NULL)
            begin
               update #sysroles4
               set mutexcl = mutexcl  +  ', '  +  @rolename  +  ' (AM)'
               from #sysroles4 r
               where mut_excl_roles (r.rolename, @rolename, 'activation') > 0
                 and mut_excl_roles (r.rolename, @rolename, 'membership') > 0

               update #sysroles4
               set mutexcl = mutexcl  +  ', '  +  @rolename  +  ' (A)'
               from #sysroles4 r
               where mut_excl_roles (r.rolename, @rolename, 'activation') > 0
                 and mut_excl_roles (r.rolename, @rolename, 'membership') = 0

               update #sysroles4
               set mutexcl = mutexcl  +  ', '  +  @rolename
               from #sysroles4 r
               where mut_excl_roles (r.rolename, @rolename, 'activation') = 0
                 and mut_excl_roles (r.rolename, @rolename, 'membership') > 0
            end

         select @seq = @seq + 1
      end

   update #sysroles4
   set mutexcl = substring (mutexcl, 4, 255)

   -- LOAD #sysroles5 WITH FORMATTED DATA

   select @melen   = max (char_length (mutexcl)),
          @rolelen = max (char_length (rolename)),
          @seq     = 2
   from #sysroles4

   if (@rolelen < 11)
      begin
         select @rolelen = 11
      end

   if (@melen < 18)
      begin
         select @melen = 18
      end

   if ((@melen + @rolelen) > 76)
      begin
         select @melen = 76 - @rolelen
      end

   insert into #sysroles5
      select rolename, mutexcl, rolename, 1
      from #sysroles4
      where mutexcl               IS NULL
         or char_length (mutexcl) <= @melen

   delete from #sysroles4
   where mutexcl               IS NULL
      or char_length (mutexcl) <= @melen

   insert into #sysroles5
      select rolename,
             substring (mutexcl, 1, 2 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen)))),
             rolename,
             1
      from #sysroles4

   update #sysroles4
   set mutexcl = ltrim (substring (mutexcl, 3 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen))), 255))

   while (exists (select *
                  from #sysroles4
                  where mutexcl IS NOT NULL))
      begin
         insert into #sysroles5
            select ' ', mutexcl, rolename, @seq
            from #sysroles4
            where char_length (mutexcl) <= @melen

         delete from #sysroles4
         where mutexcl               IS NULL
            or char_length (mutexcl) <= @melen

         insert into #sysroles5
            select ' ',
                   substring (mutexcl, 1, 2 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen)))),
                   rolename,
                   @seq
            from #sysroles4

         update #sysroles4
         set mutexcl = ltrim (substring (mutexcl, 3 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen))), 255))

         select @seq = @seq + 1
      end

   delete from #sysroles5
   where mutexcl IS NULL

   if (exists (select *
               from #sysroles5))
      begin
         select @melen = max (char_length (mutexcl))
         from #sysroles5

         select @cmdstring = 'select ''SYSTEM ROLE''=substring (rolename,1,'
                             +  convert (varchar (2), @rolelen)
                             +  '),'
                             +  '''MUTUALLY EXCLUSIVE ROLES''=substring(mutexcl,1,'
                             +  convert (varchar (2), @melen)
                             +  ')'
                             +  ' from #sysroles5'
                             +  ' order by sort, seq'

         execute  (@cmdstring)
      end
   else
      begin
         print '    NO SYSTEM ROLE IS MUTUALLY EXCLUSIVE WITH ANY OTHER ROLE.'
      end

   print ' '
go
sp_addcomment sp_sysroles, 'Lists information for all system roles'
go
sp_procxmode sp_sysroles, anymode
go

create procedure sp_userroles (@ROLE varchar (30) = NULL) 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_userroles CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if ((charindex ('sa_role', show_role ()) = 0)  and  (charindex ('sso_role', show_role ()) = 0))
      begin
         print '    ONLY AN ADMINISTRATOR OR SECURITY OFFICER CAN EXECUTE sp_userroles.'

         print ' '

         return 1
      end

   declare @actvstring   varchar ( 78),
           @attribid     smallint,
           @attribclass  smallint,
           @cmdstring1   varchar (255),
           @cmdstring2   varchar (255),
           @lnlen        tinyint,
           @loginflag    int,
           @maxsrid      smallint,
           @melen        tinyint,
           @rolename     varchar ( 30),
           @seq          smallint,
           @urlen        tinyint

   create table #userroles1
     (name     varchar ( 30)  not null,
      srid     smallint       not null,
      sub      char (3)       not null,
      pw       char (3)       not null,
      mutexcl  varchar (255)  null)

   create table #userroles2
     (name     varchar ( 30)  not null,
      srid     smallint       not null,
      sub      char (3)       not null,
      pw       char (3)       not null,
      mutexcl  varchar (255)  null,
      sort     varchar ( 30)  not null,
      seq      tinyint        not null)

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

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

   if (exists (select *
               from #userroles1))
      begin
         select @attribid    = attribute,
                @attribclass = class
         from master.dbo.sysattributes
         where object_type = 'UR'

         update #userroles1
         set sub  = 'Yes'
         from #userroles1 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 #userroles1
         set pw = 'Yes'
         where pw <> 'No'

         select @maxsrid = max (srid),
                @seq     = 1
         from master.dbo.syssrvroles

         while (@seq <= @maxsrid)
            begin
               select @rolename = role_name (@seq)

               if (@rolename IS NOT NULL)
                  begin
                     update #userroles1
                     set mutexcl = mutexcl  +  ', '  +  @rolename  +  ' (AM)'
                     from #userroles1 r
                     where mut_excl_roles (r.name, @rolename, 'activation') > 0
                       and mut_excl_roles (r.name, @rolename, 'membership') > 0

                     update #userroles1
                     set mutexcl = mutexcl  +  ', '  +  @rolename  +  ' (A)'
                     from #userroles1 r
                     where mut_excl_roles (r.name, @rolename, 'activation') > 0
                       and mut_excl_roles (r.name, @rolename, 'membership') = 0

                     update #userroles1
                     set mutexcl = mutexcl  +  ', '  +  @rolename
                     from #userroles1 r
                     where mut_excl_roles (r.name, @rolename, 'activation') = 0
                       and mut_excl_roles (r.name, @rolename, 'membership') > 0
                  end

               select @seq = @seq + 1
            end

         update #userroles1
         set mutexcl = substring (mutexcl, 4, 255)

         select @melen = coalesce (max (char_length (mutexcl)), 18),
                @urlen = max (char_length (name)),
                @seq   = 2
         from #userroles1

         if (@urlen < 9)
            begin
               select @urlen = 9
            end

         if (@melen < 18)
            begin
               select @melen = 18
            end

         if ((@melen + @urlen) > 65)
            begin
               select @melen = 65 - @urlen
            end

         insert into #userroles2
            select name, srid, sub, pw, mutexcl, name, 1
            from #userroles1
            where mutexcl               IS NULL
               or char_length (mutexcl) <= @melen

         delete from #userroles1
         where mutexcl               IS NULL
            or char_length (mutexcl) <= @melen

         insert into #userroles2
            select name,
                   srid,
                   sub,
                   pw,
                   substring (mutexcl, 1, 2 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen)))),
                   name,
                   1
            from #userroles1

         update #userroles1
         set mutexcl = ltrim (substring (mutexcl, 3 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen))), 255))

         while (exists (select *
                        from #userroles1
                        where mutexcl IS NOT NULL))
            begin
               insert into #userroles2
                  select ' ', srid, ' ', ' ', mutexcl, name, @seq
                  from #userroles1
                  where char_length (mutexcl) <= @melen

               delete from #userroles1
               where mutexcl               IS NULL
                  or char_length (mutexcl) <= @melen

               insert into #userroles2
                  select ' ',
                         srid,
                         ' ',
                         ' ',
                         substring (mutexcl, 1, 2 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen)))),
                         name,
                         @seq
                  from #userroles1

               update #userroles1
               set mutexcl = ltrim (substring (mutexcl, 3 + @melen - charindex (',', reverse (substring (mutexcl, 1, @melen))), 255))

               select @seq = @seq + 1
            end

         select @actvstring = space (3 + @urlen)  +  'SUB',
                @melen      = max (char_length (mutexcl))
         from #userroles2

         if (@melen IS NOT NULL)
            begin
               print '%1!', @actvstring

               select @cmdstring1 = 'select ''USER ROLE''=substring (name,1,'
                                    +  convert (varchar (2), @urlen)
                                    +  '),'
                                    +  '''ROLE?''=sub,'
                                    +  '''PWD?''=pw,'

               select @cmdstring2 = '''MUTUALLY EXCLUSIVE''=coalesce(substring(mutexcl,1,'
                                    +  convert (varchar (2), @melen)
                                    +  '), ''-'')'
                                    +  ' from #userroles2'
                                    +  ' order by sort, seq'

               execute  (@cmdstring1  +  @cmdstring2)
            end
         else if (@ROLE IS NULL)
            begin
               print '    NO USER ROLE IS MUTUALLY EXCLUSIVE WITH ANY OTHER ROLE.'
            end
         else
            begin
               print '    USER ROLE %1! IS NOT MUTUALLY EXCLUSIVE WITH ANY OTHER ROLE.',
                     @ROLE
            end

         print ' '

         insert into #urlogins
            select ur.name, l.name , lr.status, ur.name + ' ' + l.name
            from #userroles2 ur,
                 master.dbo.syslogins l,
                 master.dbo.sysloginroles lr
            where ur.name <> ' '
              and lr.srid  = ur.srid
              and l.suid   = lr.suid

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

               select @lnlen = max (char_length (lgn)),
                      @urlen = max (char_length (urole))
               from #urlogins

               if (@lnlen < 10)
                  begin
                     select @lnlen = 10
                  end

               if (@urlen < 10)
                  begin
                     select @urlen = 10
                  end

               select @cmdstring1 = 'select ''USER ROLE'' = substring (ur.urole, 1,'
                                    +  convert (varchar (2), @urlen)
                                    +  '),'
                                    +  '''LOGIN NAME'' = substring (ur.lgn, 1,'
                                    +  convert (varchar (2), @lnlen)
                                    +  '),'
                                    +  '''DEFAULT?'' = substring (vB.name, 1, 8)'
                                    +  ' from #urlogins ur,'
                                    +  ' sybsystemprocs.dbo.usptvalues vB'
                                    +  ' where vB.type = ''B'''
                                    +  ' and vB.number = ur.status'
                                    +  ' order by sort'

               execute  (@cmdstring1)
            end
         else if (@ROLE IS NULL)
            begin
               print '    THERE ARE NO LOGINS DIRECTLY GRANTED ANY USER ROLE.'
            end
         else
            begin
               print '    THERE ARE NO LOGINS DIRECTLY GRANTED ROLE %1!.', @ROLE

               print ' '
            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

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

dump transaction sybsystemprocs with truncate_only
go
