use sybsystemprocs
go
drop procedure sp_group
go
drop procedure sp_groups
go
drop procedure sp_members
go
drop procedure sp_sysroles
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 ((@userid = 0)  or  (@userid > 16389))
      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 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,
       memcnt      smallint  not null,
       grantcnt    smallint  not null,
       execcnt     smallint  not null,
       selcnt      smallint  not null,
       delcnt      smallint  not null,
       inscnt      smallint  not null,
       updcnt      smallint  not null,
       refcnt      smallint  not null,
       createcnt   smallint  not null,
       dumpcnt     tinyint   not null)

   insert into #groups
      select ug.uid, count (uu.uid), 0, 0, 0, 0, 0, 0, 0, 0, 0
      from sysusers ug,
           sysusers uu
      where ug.uid *= uu.gid                              -- map group to users
        and ((uu.uid < 16384)  and  (uu.uid > 0))         -- user row
        and ((ug.uid > 16389)  or  (ug.uid = 0))          -- group row
      group by ug.uid

   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 (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

         select 'GROUP'      = substring (user_name (uid), 1, 7),
                'MEMBERS'    = substring (convert (char (10), convert (money, memcnt), 1), 1, 7),
                'GRANTS'     = substring (convert (char (9), convert (money, grantcnt), 1), 1, 6),
                'EXECUTE'    = substring (convert (char (10), convert (money, execcnt), 1), 1, 7),
                'SELECT'     = substring (convert (char (9), convert (money, selcnt), 1), 1, 6),
                'DELETE'     = substring (convert (char (9), convert (money, delcnt), 1), 1, 6),
                'INSERT'     = substring (convert (char (9), convert (money, inscnt), 1), 1, 6),
                'UPDATE'     = substring (convert (char (9), convert (money, updcnt), 1), 1, 6),
                'REF'        = substring (convert (char (9), convert (money, refcnt), 1), 1, 6),
                'CREATE'     = substring (convert (char (8), convert (money, createcnt), 1), 1, 5),
                'DUMP'       = substring (convert (char (7), convert (money, dumpcnt), 1), 1, 4)
         from #groups
         order by user_name (uid)
      end
   else
      begin
         select 'GROUP'      = substring (user_name (uid), 1, 13),
                'MEMBERS'    = substring (convert (char (10), convert (money, memcnt), 1), 1, 7),
                'GRANTS'     = substring (convert (char (9), convert (money, grantcnt), 1), 1, 6),
                'EXECUTE'    = substring (convert (char (10), convert (money, execcnt), 1), 1, 7),
                'SELECT'     = substring (convert (char (9), convert (money, selcnt), 1), 1, 6),
                'DELETE'     = substring (convert (char (9), convert (money, delcnt), 1), 1, 6),
                'INSERT'     = substring (convert (char (9), convert (money, inscnt), 1), 1, 6),
                'UPDATE'     = substring (convert (char (9), convert (money, updcnt), 1), 1, 6),
                'REF'        = substring (convert (char (8), convert (money, refcnt), 1), 1, 5),
                'CREATE'     = substring (convert (char (9), convert (money, createcnt), 1), 1, 6)
         from #groups
         order by user_name (uid)
      end
go
sp_addcomment sp_groups, 12, 'Lists all groups with aggregate data'
go
sp_procxmode sp_groups, anymode
go

create procedure sp_members (@GROUP 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_members> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if (exists (select *
               from sysusers
               where name = isnull (@GROUP, name)
                 and ((uid = 0)  or  (uid > 16383))))
      begin
            select 'GROUP' = space (30),
                   MEMBER  = space (30)
            where 1 = 2
         union
            select ug.name, uu.name
            from sysusers ug, sysusers uu
            where ug.name = isnull (@GROUP, ug.name)
              and ((ug.uid > 16389)  or  (ug.uid = 0))    -- group row
              and uu.gid  = ug.uid                        -- map user to group
              and ((uu.uid < 16384)  and  (uu.uid > 0))   -- user row
         union
            select ug.name, '(none)'
            from sysusers ug
            where @GROUP IS NULL
              and ((ug.uid > 16389)  or  (ug.uid = 0))    -- group row
              and not exists (select uu.*
                              from sysusers uu
                              where uu.gid = ug.uid
                                and uu.uid > 0
                                and uu.uid < 16384)
         order by 'GROUP', MEMBER
      end
   else
      begin
         print '    THERE IS NO GROUP <%1!>.', @GROUP

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

create procedure sp_sysroles 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

   select 'ROLE' = substring (sr.name, 1, 17),
          'USER' = isnull (suser_name (lr.suid), '(none)')
   from master.dbo.sysloginroles lr,
        master.dbo.syssrvroles sr
   where sr.srid *= lr.srid
   order by sr.name, suser_name (lr.suid)
go
sp_addcomment sp_sysroles, 12, 'Lists the users with system roles'
go
sp_procxmode sp_sysroles, anymode
go

dump transaction sybsystemprocs with truncate_only
go
