use sybsystemprocs
go

sp_dropprocedure sp_logins_like
go
sp_dropprocedure sp_logins_pwddate
go
sp_dropprocedure sp_logins_roles
go
sp_dropprocedure sp_logins_status
go
sp_dropprocedure sp_logins
go
sp_dropprocedure sp_users_alias
go
sp_dropprocedure sp_users_all
go
sp_dropprocedure sp_users_like
go
sp_dropprocedure sp_users_roles
go
sp_dropprocedure sp_users
go

set quoted_identifier on
go

------------- Temporary table needed for creation of sp_logins subroutines --

create table #logins
  (suid        smallint                          not null,
   lname       varchar (30)                      not null,
   rname       varchar (32)  default ' '         not null,
   pwddate     datetime                          null,
   dbname      varchar (30)  default ' '         not null,
   locked      char (7)      default '     No'   not null,
   badpwd      char (4)      default '  No'      not null,
   minpwdexp   smallint      default 0           null,
   minpwdlen   smallint      default 0           null,
   maxlgnfail  smallint      default 0           null,
   status      smallint      default 0           not null,
   srlcount    smallint      default 0           not null,
   urlcount    smallint      default 0           not null,
   actvcount   smallint      default 0           not null,
   rlgncount   smallint      default 0           not null,
   lgnfailcnt  smallint      default 0           null,
   sort        varchar (61)  default ' '         not null)
go

create procedure sp_logins_like (@PARM     varchar (30),
                                 @OFFICER  tinyint)  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @cmdstring    varchar (255),
            @dblen        tinyint,
            @likestring   varchar ( 30),
            @loginlen     tinyint,
            @printstring  varchar ( 78)

   execute  sp_like_string  @INPUT_STRING=@PARM,
                            @LIKE_STRING=@likestring  output

   insert into #logins (suid, lname, pwddate, dbname, lgnfailcnt)
      select suid, name, pwdate, dbname, coalesce (logincount, 0)
      from master.dbo.syslogins
      where name like @likestring

   if (exists (select *
                from #logins))
      begin
         if (@OFFICER = 1)
            begin
               update #logins
               set srlcount  = (select count (lr.suid)
                                from master.dbo.sysloginroles lr
                                where lr.suid = l.suid
                                  and lr.srid < 32),
                   urlcount  = (select count (lr.suid)
                                from master.dbo.sysloginroles lr
                                where lr.suid = l.suid
                                  and lr.srid > 31),
                   actvcount = (select count (lr.suid)
                                from master.dbo.sysloginroles lr
                                where lr.suid   = l.suid
                                  and lr.srid   > 31
                                  and lr.status > 0),
                   rlgncount = (select count (rl.suid)
                                from master.dbo.sysremotelogins rl
                                where rl.suid = l.suid)
               from #logins l

               select @dblen    = max (char_length (dbname)),
                      @loginlen = max (char_length (lname))
               from #logins

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

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

               if ((@dblen + @loginlen) > 39)
                  begin
                     while ((@dblen + @loginlen) > 39)
                        begin
                           if (@dblen > 10)
                              begin
                                 select @dblen = @dblen - 1
                              end

                           if (@loginlen > 10)
                              begin
                                 select @loginlen = @loginlen - 1
                              end
                        end

                     select @loginlen = 39 - @dblen
                  end

               select @printstring = space (12 + @loginlen + @dblen)  +  'SYSTEM  USER  ACTIVE REMOTE'

               print '%1!', @printstring
            end
         else
            begin
               select @loginlen = max (char_length (lname))
               from #logins
            end

         select @cmdstring = 'select ''LOGIN NAME'' = substring (lname, 1,'
                             +  convert (varchar (2), @loginlen)
                             +  ')'

         if (@OFFICER = 1)
            begin
               select @cmdstring = @cmdstring
                                   +  ','
                                   +  '''DEFAULT DB'' = substring (dbname, 1,'
                                   +  convert (varchar (2), @dblen)
                                   +  '),'
                                   +  '''PWD DATE'' = convert (char (8), pwddate, 11),'
                                   +  '''ROLES '' = srlcount,'
                                   +  'ROLES = urlcount,'
                                   +  'DFLTS = actvcount,'
                                   +  'LOGINS = rlgncount'
            end

         select @cmdstring = @cmdstring
                             +  ' from #logins'
                             +  ' order by lname'

         execute (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO LOGINS LIKE %1!.', @likestring

         print ' '
      end
go
sp_existsprocedure sp_logins_like
go

create procedure sp_logins_pwddate as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @cmdstring        varchar (255),
            @loginlen         tinyint,
            @pwd_attribclass  int,
            @printstring      varchar ( 78),
            @sysminpwdexp     int

   select @pwd_attribclass = class
   from master.dbo.sysattributes
   where object_type = 'PS'

   select @sysminpwdexp = value
   from master.dbo.sysconfigures
   where name = 'systemwide password expiration'

   insert into #logins (suid, lname, pwddate)
      select suid, name, pwdate
      from master.dbo.syslogins

   select @loginlen = max (char_length (lname))
   from #logins

   select @printstring = space (20 + @loginlen)  +  'DAYS TO'

   print '%1!', @printstring

   update #logins
   set minpwdexp  = (select a.int_value - datediff (dd, l.pwddate, getdate ())
                     from master.dbo.sysattributes a
                     where a.class        = @pwd_attribclass
                       and a.attribute    = 0
                       and a.object_type  = 'PS'
                       and a.object       = l.suid
                       and a.object_cinfo = 'login')
   from #logins l

   if (@sysminpwdexp <> 0)
      begin
         update #logins
         set minpwdexp  = @sysminpwdexp - datediff (dd, pwddate, getdate ())
         where minpwdexp IS NULL
      end

   select @cmdstring = 'select ''LOGIN NAME''=substring(lname,1,'
                       +  convert (varchar (2), @loginlen)
                       +  '),'
                       +  '''PASSWORD DATE/TIME''=convert(char(8),pwddate,11)'
                       +  '+''  '''
                       +  '+convert(char(8),pwddate,8),'
                       +  'EXPIRE=coalesce(substring(convert(char(9),convert(money,minpwdexp),1),1,6),''     -'')'
                       +  ' from #logins'
                       +  ' order by pwddate, lname'

   execute  (@cmdstring)
go
sp_existsprocedure sp_logins_pwddate
go

create procedure sp_logins_roles as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @actvtext    varchar ( 78),
            @actvllen    tinyint,
            @actvrlen    tinyint,
            @cmdstring   varchar (255),
            @loginlen    tinyint,
            @rolelen     tinyint

   insert into #logins (suid, lname, rname, status, sort)
      select l.suid, l.name, sr.name, lr.status, l.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.srid      > 31
        and sr.password IS NULL
        and lr.srid      = sr.srid
        and l.suid       = lr.suid

   insert into #logins (suid, lname, rname, status, sort)
      select l.suid, l.name, sr.name + ' (PWD)', lr.status, l.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where sr.srid      > 31
        and sr.password IS NOT NULL
        and lr.srid      = sr.srid
        and l.suid       = lr.suid

   if (exists (select *
               from #logins))
      begin
         update #logins
         set lname = ' '
         from #logins u1
         where exists (select *
                       from #logins u2
                       where u2.lname = u1.lname
                         and u2.rname = u1.rname
                         and u2.rname > (select min (rname)
                                         from #logins u3
                                         where u3.lname = u2.lname))

         select @loginlen = max (char_length (lname)),
                @rolelen  = max (char_length (rname))
         from #logins

         if (@loginlen < 10)
            begin
               select @actvllen = 10
            end
         else
            begin
               select @actvllen = @loginlen
            end

         if (@rolelen < 9)
            begin
               select @actvrlen = 9
            end
         else
            begin
               select @actvrlen = @rolelen
            end

         select @actvtext = space (@actvllen + @actvrlen + 4)  +  'ACTIVE'

         print @actvtext

         select @cmdstring = 'select ''LOGIN NAME'' = substring (l.lname, 1,'
                             +  convert (varchar (2), @loginlen)
                             +  '),'
                             +  '''USER ROLE'' = substring (l.rname, 1,'
                             +  convert (varchar (2), @rolelen)
                             +  '),'
                             +  '''DEFAULT?'' = substring (vB.name, 1, 8)'
                             +  ' from #logins l,'
                             +  ' sybsystemprocs.dbo.usptvalues vB'
                             +  ' where vB.type = ''B'''
                             +  ' and vB.number = l.status'
                             +  ' order by l.sort'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO USER ROLES FOR ANY LOGIN.'

         print ' '
      end
go
sp_existsprocedure sp_logins_roles
go

create procedure sp_logins_status as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @pwd_attribclass int,
            @sysmaxlgnfail   int,
            @sysminpwdexp    int,
            @sysminpwdlen    int

   if (exists (select *
               from master.dbo.syslogins
               where status     > 0
                  or logincount > 0))
      begin
         print '                                             MIN  DAYS   MAX   RECENT'

         print '                                BAD  ACCOUNT PWD   TO   LOGIN  LOGIN'

         select @pwd_attribclass = class
         from master.dbo.sysattributes
         where object_type = 'PS'

         select @sysmaxlgnfail = value
         from master.dbo.sysconfigures
         where name = 'maximum failed logins'

         select @sysminpwdexp = value
         from master.dbo.sysconfigures
         where name = 'systemwide password expiration'

         select @sysminpwdlen = value
         from master.dbo.sysconfigures
         where name = 'minimum password length'

         insert into #logins (suid, lname, pwddate, status, lgnfailcnt)
            select suid, name, pwdate, status, logincount
            from master.dbo.syslogins
            where status     > 0
               or logincount > 0

         update #logins
         set minpwdexp  = (select a.int_value - datediff (dd, l.pwddate, getdate ())
                           from master.dbo.sysattributes a
                           where a.class        = @pwd_attribclass
                             and a.attribute    = 0
                             and a.object_type  = 'ps'
                             and a.object       = l.suid
                             and a.object_cinfo = 'login'),
             minpwdlen  = (select a.int_value
                           from master.dbo.sysattributes a
                           where a.class        = @pwd_attribclass
                             and a.attribute    = 1
                             and a.object_type  = 'ps'
                             and a.object       = l.suid
                             and a.object_cinfo = 'login'),
             maxlgnfail = (select a.int_value
                           from master.dbo.sysattributes a
                           where a.class        = @pwd_attribclass
                             and a.attribute    = 2
                             and a.object_type  = 'ps'
                             and a.object       = l.suid
                             and a.object_cinfo = 'login')
         from #logins l

         if (@sysminpwdexp <> 0)
            begin
               update #logins
               set minpwdexp  = @sysminpwdexp - datediff (dd, pwddate, getdate ())
               where minpwdexp IS NULL
            end

         update #logins
         set minpwdlen = @sysminpwdlen
         where minpwdlen IS NULL

         update #logins
         set maxlgnfail = @sysmaxlgnfail
         where maxlgnfail IS NULL

         update #logins
         set badpwd = ' yes'
         where (status & 1) = 1

         update #logins
         set locked = '    Yes'
         where (status & 2) = 2

         select 'LOGIN NAME' = lname,
                'PWD?'       = badpwd,
                'LOCKED?'    = locked,
                LEN          = convert (tinyint, minpwdlen),
                EXPIRE       = coalesce (substring (convert (char (9),
                                                             convert (money,
                                                                      minpwdexp),
                                                             1),
                                                    1,
                                                    6),
                                         '     -'),
                FAILS        = convert (smallint, maxlgnfail),
                'FAILS '     = coalesce (substring (convert (char (9),
                                                             convert (money,
                                                                      lgnfailcnt),
                                                             1),
                                                    1,
                                                    6),
                                         '     -')
         from #logins
         order by lname
      end
   else
      begin
         print '    THERE ARE NO LOGINS WITH LOCKED/EXPIRED/BAD ACCOUNTS.'

         print ' '
      end
go
sp_existsprocedure sp_logins_status
go

drop table #logins
go


sp_addcomment sp_logins, 'Lists logins with various scope/sort options:'
go
sp_addcomment sp_logins, '   NULL:       all logins in name order (default)'
go
sp_addcomment sp_logins, '   PREFIX:     only logins like PREFIX% in login name order'
go
sp_addcomment sp_logins, '   "ROLES":    only logins with user roles in login/role name order'
go
sp_addcomment sp_logins, '   "STATUS":   only logins with locked/expired/bad accounts'
go
sp_addcomment sp_logins, '   "USAGE":    all logins in CPU and I/O usage order'
go
sp_procxmode sp_logins, anymode
go

------------ Temporary table needed for creation of sp_users subroutines ----

create table #users
  (uname    varchar (30)  default ' '  not null,
   grname   varchar (32)  default ' '  not null,
   lname    varchar (32)               not null,
   status   smallint      default 0    not null,
   sort     varchar (61)  default ' '  not null)
go

create procedure sp_users_alias as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @cmdstring  varchar (255),
            @dbname     varchar ( 30),
            @grplen     tinyint,
            @loginlen   tinyint,
            @userlen    tinyint

   insert into #users
      select u.name, g.name, l.name + ' $', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysusers g,
           sysusers u
      where l.suid  = u.suid
        and l.name <> u.name
        and u.name <> 'dbo'
        and g.uid   = u.gid

   insert into #users
      select u.name, g.name, l.name + ' #', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysalternates a,
           sysusers g,
           sysusers u
      where a.suid = l.suid
        and u.suid = a.altsuid
        and g.uid  = u.gid

   if (exists (select *
               from #users))
      begin
         print '    "#" INDICATES AN ALIAS AND "$" INDICATES A PSEUDONYM.'

         print ' '

         update #users
         set uname  = ' ',
             grname = ' '
         from #users u
         where exists (select u2.lname
                       from #users u2
                       where u2.uname = u.uname
                         and u2.lname = u.lname
                         and u2.lname > (select min (u3.lname)
                                         from #users u3
                                         where u3.uname = u2.uname))

         select @userlen  = max (char_length (uname)),
                @grplen   = max (char_length (grname)),
                @loginlen = max (char_length (lname))
         from #users

         select @cmdstring = 'select ''USER NAME'' = substring (uname, 1,'
                             +  convert (varchar (2), @userlen)
                             +  '),'
                             +  '''USER GROUP'' = substring (grname, 1,'
                             +  convert (varchar (2), @grplen)
                             +  '),'
                             +  '''LOGIN NAME'' = substring (lname, 1,'
                             +  convert (varchar (2), @loginlen)
                             +  ')'
                             +  ' from #users'
                             +  ' order by sort'

         execute  (@cmdstring)
      end
   else
      begin
         select @dbname = db_name ()

         print '    THERE ARE NO ALIASES OR PSEUDONYMS IN DATABASE %1!.',
               @dbname

         print ' '
      end
go
sp_existsprocedure sp_users_alias
go

create procedure sp_users_all as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @cmdstring  varchar (255),
            @grplen     tinyint,
            @loginlen   tinyint,
            @userlen    tinyint

   insert into #users
      select 'guest', g.name, '-', 0, 'guest '
      from master.dbo.sysdatabases d,
           sysusers g,
           sysusers u
      where d.dbid = 1
        and u.name = 'guest'
        and g.uid  = u.gid

   insert into #users
      select u.name, g.name, l.name, 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysusers g,
           sysusers u
      where l.suid = u.suid
        and ((l.name = u.name)  or  (u.name = 'dbo'))
        and g.uid  = u.gid

   insert into #users
      select u.name, g.name, l.name + ' $', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysusers g,
           sysusers u
      where l.suid  = u.suid
        and l.name <> u.name
        and u.name <> 'dbo'
        and g.uid   = u.gid

   insert into #users
      select u.name, g.name, l.name + ' #', 0, u.name + ' ' + l.name
      from master.dbo.syslogins l,
           sysalternates a,
           sysusers g,
           sysusers u
      where a.suid = l.suid
        and u.suid = a.altsuid
        and g.uid  = u.gid

   if (exists (select *
               from #users
               where lname like '% #'
                  or lname like '% $'))
      begin
         print '    "#" INDICATES AN ALIAS AND "$" INDICATES A PSEUDONYM.'

         print ' '

         update #users
         set uname  = ' ',
             grname = ' '
         from #users u
         where exists (select u2.lname
                       from #users u2
                       where u2.uname = u.uname
                         and u2.lname = u.lname
                         and u2.lname > (select min (u3.lname)
                                         from #users u3
                                         where u3.uname = u2.uname))
      end

   select @userlen  = max (char_length (uname)),
          @grplen   = max (char_length (grname)),
          @loginlen = max (char_length (lname))
   from #users

   select @cmdstring = 'select ''USER NAME'' = substring (uname, 1,'
                       +  convert (varchar (2), @userlen)
                       +  '),'
                       +  '''USER GROUP'' = substring (grname, 1,'
                       +  convert (varchar (2), @grplen)
                       +  '),'
                       +  '''LOGIN NAME'' = substring (lname, 1,'
                       +  convert (varchar (2), @loginlen)
                       +  ')'
                       +  ' from #users'
                       +  ' order by sort'

   execute  (@cmdstring)
go
sp_existsprocedure sp_users_all
go

create procedure sp_users_like (@PARM  varchar (30))
                 with recompile as

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @cmdstring   varchar (255),
           @grplen      tinyint,
           @likestring  varchar ( 30),
           @loginlen    tinyint,
           @userlen     tinyint

   execute  sp_like_string  @INPUT_STRING=@PARM,
                            @LIKE_STRING=@likestring  output

   if ((exists (select *
                from master.dbo.syslogins
                where name like @likestring))  or
       (exists (select *
                from sysusers
                where name like @likestring)))
      begin
         insert into #users
            select u.name, g.name, '-', 0, ' '
            from master.dbo.sysdatabases d,
                 sysusers g,
                 sysusers u
            where d.dbid    = 1
              and u.name like @likestring
              and u.name    = 'guest'
              and g.uid     = u.gid

         insert into #users
            select u.name, g.name, l.name, 0, ' '
            from master.dbo.syslogins l,
                 sysusers g,
                 sysusers u
            where ((l.name like @likestring)  or  (u.name like @likestring))
              and u.suid = l.suid
              and ((u.name = l.name)  or  (u.name = 'dbo'))
              and g.uid  = u.gid

         insert into #users
            select u.name + ' $', g.name, l.name, 0, ' '
            from master.dbo.syslogins l,
                 sysusers g,
                 sysusers u
            where ((l.name like @likestring)  or  (u.name like @likestring))
              and u.suid  = l.suid
              and u.name <> l.name
              and u.name <> 'dbo'
              and g.uid   = u.gid

         insert into #users
            select u.name + ' #', g.name, l.name, 0, ' '
            from master.dbo.syslogins l,
                 sysalternates a,
                 sysusers g,
                 sysusers u
            where ((l.name like @likestring)  or  (u.name like @likestring))
              and a.suid    = l.suid
              and u.suid    = a.altsuid
              and g.uid     = u.gid

         if (exists (select *
                     from #users
                     where uname like '% #'
                        or uname like '% $'))
            begin
               print '    "#" INDICATES AN ALIAS AND "$" INDICATES A PSEUDONYM.'

               print ' '
            end

         insert into #users
            select '-', '-', l.name, 0, ' '
            from master.dbo.syslogins l
            where l.name like @likestring
              and not exists (select a.*
                              from sysalternates a
                              where a.suid = l.suid)
              and not exists (select u.*
                              from sysusers u
                              where u.suid = l.suid)

         select @userlen  = max (char_length (uname)),
                @grplen   = max (char_length (grname)),
                @loginlen = max (char_length (lname))
         from #users

         select @cmdstring = 'select ''USER NAME'' = substring (uname, 1,'
                             +  convert (varchar (2), @userlen)
                             +  '),'
                             +  '''USER GROUP'' = substring (grname, 1,'
                             +  convert (varchar (2), @grplen)
                             +  '),'
                             +  '''LOGIN NAME'' = substring (lname, 1,'
                             +  convert (varchar (2), @loginlen)
                             +  ')'
                             +  ' from #users'
                             +  ' order by lname'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO USERS/LOGINS LIKE %1!.', @likestring

         print ' '
      end
go
sp_existsprocedure sp_users_like
go

create procedure sp_users_roles  with recompile  as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare  @actvtext   varchar ( 72),
            @actvrlen   tinyint,
            @actvulen   tinyint,
            @cmdstring  varchar (255),
            @rolelen    tinyint,
            @userlen    tinyint

   insert into #users
      select u.name, sr.name, ' ', lr.status, u.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr,
           sysroles r,
           sysusers u
      where sr.srid      > 31
        and sr.password IS NULL
        and r.id         = sr.srid
        and lr.srid      = sr.srid
        and l.suid       = lr.suid
        and u.suid       = l.suid

   insert into #users
      select u.name, sr.name + ' #', ' ', lr.status, u.name + ' ' + sr.name
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr,
           sysroles r,
           sysusers u
      where sr.srid      > 31
        and sr.password IS NOT NULL
        and r.id         = sr.srid
        and lr.srid      = sr.srid
        and l.suid       = lr.suid
        and u.suid       = l.suid

   if (exists (select *
               from #users))
      begin
         update #users
         set uname = ' '
         from #users u1
         where exists (select *
                       from #users u2
                       where u2.uname  = u1.uname
                         and u2.grname = u1.grname
                         and u2.grname > (select min (grname)
                                          from #users u3
                                          where u3.uname = u2.uname))

         select @rolelen = max (char_length (grname)),
                @userlen = max (char_length (uname))
         from #users

         if (@userlen < 9)
            begin
               select @actvulen = 9
            end
         else
            begin
               select @actvulen = @userlen
            end

         if (@rolelen < 9)
            begin
               select @actvrlen = 9
            end
         else
            begin
               select @actvrlen = @rolelen
            end

         select @actvtext = space (@actvulen + @actvrlen + 4)  +  'ACTIVE'

         print @actvtext

         select @cmdstring = 'select ''USER NAME'' = substring (u.uname, 1,'
                             +  convert (varchar (2), @userlen)
                             +  '),'
                             +  '''USER ROLE'' = substring (u.grname, 1,'
                             +  convert (varchar (2), @rolelen)
                             +  '),'
                             +  '''DEFAULT?'' = substring (vB.name, 1, 8)'
                             +  ' from #users u,'
                             +  ' sybsystemprocs.dbo.usptvalues vB'
                             +  ' where vB.type = ''B'''
                             +  ' and vB.number = u.status'
                             +  ' order by sort'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO USER ROLES FOR ANY USER.'

         print ' '
      end
go
sp_existsprocedure sp_users_roles
go

drop table #users
go


create procedure sp_users (@PARM   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_users CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   create table #users
     (uname    varchar (30)  default ' '  not null,
      grname   varchar (32)  default ' '  not null,
      lname    varchar (32)               not null,
      status   smallint      default 0    not null,
      sort     varchar (61)  default ' '  not null)

   if ((user_id () = 1)  and
       (not exists (select *
                    from master.dbo.sysdatabases
                    where dbid            = db_id ()
                      and (status & 1024) = 1024)))
      begin
         print '    UPDATING INDEX STATISTICS ON SYSTEM TABLES.'

         print ' '

         update index statistics sysalternates

         update index statistics sysroles

         update index statistics sysusers
      end

   if (upper (@PARM) = 'ALIAS')
      begin
         execute  sp_users_alias
      end
   else if (upper (@PARM) = 'ROLES')
      begin
         if ((user_id () = 1)                            or
             (charindex ('sso_role', show_role ()) > 0)  or
             (charindex ('sa_role', show_role ()) > 0))
            begin
               execute  sp_users_roles
            end
         else
            begin
               print '    ONLY AN ADMINISTRATOR, SECURITY OFFICER, OR THE DBO CAN EXECUTE sp_users roles.'

               print ' '

               return 1
            end
      end
   else if (@PARM IS NULL)
      begin
         execute  sp_users_all
      end
   else
      begin
         execute  sp_users_like  @PARM=@PARM
      end
go
sp_addcomment sp_users, 'Lists users with various scope/sort options:'
go
sp_addcomment sp_users, '   (NULL):  all users (default)'
go
sp_addcomment sp_users, '   "ALIAS": only users with an alias or pseudonym'
go
sp_addcomment sp_users, '   PREFIX:  only users/logins like PREFIX%'
go
sp_addcomment sp_users, '   "ROLES": only users that have user roles'
go
sp_procxmode sp_users, anymode
go

update index statistics udescriptions
go

dump transaction sybsystemprocs with truncate_only
go
