use sybsystemprocs
go

drop procedure sp_columnsunique
go
drop procedure sp_deviceright
go
drop procedure sp_domainsright_col_cnstrnts
go
drop procedure sp_domainsright_col_ut_rules
go
drop procedure sp_domainsright
go
drop procedure sp_groupsright_load
go
drop procedure sp_groupsright_print
go
drop procedure sp_groupsright
go
drop procedure sp_objectsunique
go
drop procedure sp_protectsright         -- In 1151A release
go
drop procedure sp_refkeysright
go
drop procedure sp_rolesright
go
drop procedure sp_usesright
go

set quoted_identifier on
go

create procedure sp_columnsunique (@NULL_FLAG varchar (1) = 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_columnsunique> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @colcount   varchar (5),
           @diffcount  varchar (5)

   create table #columnsunique
      (uname       varchar (12)  not null,
       objectname  varchar (24)  not null,
       columnname  varchar (20)  not null,
       typetext    varchar (20)  not null,
       utype       smallint      not null,
       stype       tinyint       not null,
       len         tinyint       not null,
       prec        tinyint       null,
       scale       tinyint       null,
       nulltype    int           not null,
       sortorder   varchar (30)  null)

   if (@NULL_FLAG IS NULL)
      begin
         print '    CHECKING WILL BE BY TYPE AND LENGTH/PRECISION/SCALE.'

         insert into #columnsunique
            select substring (isnull (user_name (o.uid), '?'), 1, 12),
                   substring (o.name, 1, 24),
                   substring (c.name, 1, 18),
                   substring (t.name, 1, 20),
                   c.usertype,
                   c.type,
                   c.length,
                   c.prec,
                   c.scale,
                   convert (int, convert (bit, (c.status & 8))),
                   NULL
            from syscolumns c,
                 syscolumns c2,
                 sysobjects o,
                 sysobjects o2,
                 systypes   t
            where c.id               = o.id
              and (o.sysstat & 15)  in (2, 3)
              and c2.id              = o2.id
              and (o2.sysstat & 15) in (2, 3)
              and c.name             = c2.name
              and c.number           = 0
              and c.usertype         = t.usertype
              and c2.number          = 0
              and (c.usertype <> c2.usertype  or
                   c.length   <> c2.length    or
                   c.prec     <> c2.prec      or
                   c.scale    <> c2.scale)
      end
   else
      begin
         print '    CHECKING WILL BE BY TYPE, LENGTH/PRECISION/SCALE AND NULLABILITY.'

         insert into #columnsunique
            select substring (isnull (user_name (o.uid), '?'), 1, 12),
                   substring (o.name, 1, 24),
                   substring (c.name, 1, 18),
                   substring (t.name, 1, 20),
                   c.usertype,
                   c.type,
                   c.length,
                   c.prec,
                   c.scale,
                   convert (int, convert (bit, (c.status & 8))),
                   NULL
            from syscolumns c,
                 syscolumns c2,
                 sysobjects o,
                 sysobjects o2,
                 systypes   t
            where c.id               = o.id
              and (o.sysstat & 15)  in (2, 3)
              and c2.id              = o2.id
              and (o2.sysstat & 15) in (2, 3)
              and c.name             = c2.name
              and c.number           = 0
              and c.usertype         = t.usertype
              and c2.number          = 0
              and (c.usertype     <> c2.usertype  or
                   c.length       <> c2.length    or
                   c.prec         <> c2.prec      or
                   c.scale        <> c2.scale     or
                   (c.status & 8) <> (c2.status & 8))
      end

   print ' '

   if (exists (select *
               from #columnsunique))
      begin
         update #columnsunique
         set sortorder = substring (columnname + ' ' + objectname + ' ' + uname,
                                    1,
                                    30)

         select @colcount  = ltrim (substring (convert (char (8),
                                                        convert (money,
                                                                 count (distinct columnname)),
                                                        1),
                                               1,
                                               5)),
                @diffcount = ltrim (substring (convert (char (8),
                                                        convert (money,
                                                                 count (distinct sortorder)),
                                                        1),
                                               1,
                                               5))
         from #columnsunique

         print '    THERE ARE %1! COLUMN NAMES WITH %2! POTENTIAL TYPE DIFFERENCES.',
               @colcount, @diffcount

         print ' '

         update #columnsunique
         set len = len / @@ncharsize
         where ((utype = 24)  and  (stype = 47))
            or ((utype = 25)  and  (stype = 39))

         update #columnsunique
         set typetext = typetext
                        + ' ('
                        + convert (varchar (3), len)
                        + ')'
         where utype in (1, 2, 3, 4, 24, 25)

         update #columnsunique
         set typetext = typetext
                        + ' ('
                        + convert (varchar (3), prec)
                        + ', '
                        + convert (varchar (3), scale)
                        + ')'
         where utype in (10, 26)

         update #columnsunique
         set typetext = 'float (' + convert (varchar (3), prec) + ')'
         where utype in (8, 23)
           and prec  IS NOT NULL

         update #columnsunique
         set typetext = 'real'
         where utype  = 23
           and prec  IS NULL

         update #columnsunique
         set typetext = 'double precision'
         where utype  = 8
           and prec  IS NULL

         update #columnsunique
         set typetext = 'tinyint'
         where utype in (5, 13)
           and len    = 1

         update #columnsunique
         set typetext = 'smallint'
         where utype in (6, 13)
           and len    = 2

         update #columnsunique
         set typetext = 'int'
         where utype in (7, 13)
           and len    = 4

         update #columnsunique
         set typetext = typetext + ' *'
         where nulltype = 1

         insert into #columnsunique
            select ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, min (sortorder)
            from #columnsunique
            group by columnname

         delete from #columnsunique
         where columnname = ' '
           and sortorder  = (select min (sortorder)
                             from #columnsunique)

         select distinct 'OWNER'               = uname,
                         'TABLE/VIEW'          = objectname,
                         'COLUMN'              = substring (columnname, 1, 18),
                         'TYPE (* = NULLABLE)' = typetext
         from #columnsunique
         order by sortorder, columnname
      end
   else if (@NULL_FLAG IS NULL)
      begin
         print '    ALL USER COLUMN NAMES ARE OF UNIQUE TYPE AND LENGTH.'

         print ' '
      end
   else
      begin
         print '    ALL USER COLUMN NAMES ARE OF UNIQUE TYPE, LENGTH AND NULLABILITY.'

         print ' '
      end
go
sp_addcomment sp_columnsunique, 11, 'Checks consistency of user column names by type/length'
go
sp_addcomment sp_columnsunique, 11, '   if NULL_FLAG is non-null check by type/length/nullability'
go
sp_procxmode sp_columnsunique, anymode
go

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

         print ' '

         return 1
      end

   create table #unuseddvc
      (vdevno   tinyint        not null)

   create table #mrdv
      (low      int            not null,
       high     int            not null,
       sybname  varchar (30)   not null,
       number   smallint       not null,
       phyname  varchar (127)  null,
       status   smallint       not null,
       C        smallint       not null,
       T        smallint       not null)

   create table #mru
      (dbid     smallint       not null,
       segmap   int            not null,
       size     int            not null,
       vstart   int            not null,
       dbname   varchar (30)   not null,
       usage    char (12)      not null,
       pages    char (14)      not null)


   insert into #unuseddvc
      select number
      from sybsystemprocs.dbo.usptvalues
      where type    = 'P'
        and number >= 1
        and number <= (select max (convert (tinyint, low / power (2, 24)))
                       from master.dbo.sysdevices)

   delete #unuseddvc
   from #unuseddvc u
   where exists (select d.low
                 from master.dbo.sysdevices d
                 where convert (tinyint, d.low / power (2, 24)) = u.vdevno)

   if (exists (select *
               from #unuseddvc))
      begin
         print '    THERE ARE GAPS IN THE VIRTUAL DEVICE NUMBERS.'

         print ' '

         select 'MISSING DEVICE NUMBER' = vdevno
         from #unuseddvc
         order by vdevno
      end
   else
      begin
         print '    THE VIRTUAL DEVICE NUMBERS ARE CONSECUTIVE.'
      end

   print ' '

   if (not exists (select *
                   from master.dbo.sysdevices
                   where cntrltype   = 0
                     and mirrorname IS NOT NULL))
      begin
         print '    THERE ARE NO MIRRORED DEVICES.'
      end
   else
      begin
         if (exists (select *
                     from master.dbo.sysdevices
                     where cntrltype = 0
                       and substring (phyname, 1, charindex ('d0s', phyname) -1)
                           = substring (mirrorname, 1, charindex ('d0s', mirrorname) -1)))
            begin
               print '    THERE ARE MIRRORED DEVICES ON THE SAME DISK.'

               print ' '

               select 'DEVICE' = substring (name, 1, 25),
                      PHYSICAL = substring (phyname, 1, 25),
                      'MIRROR' = substring (mirrorname, 1, 25)
               from master.dbo.sysdevices
               where cntrltype = 0
                 and substring (phyname, 1, charindex ('d0s', phyname) -1)  =
                     substring (mirrorname, 1, charindex ('d0s', mirrorname) -1)
               order by name
            end
         else if (exists (select *
                          from master.dbo.sysdevices
                          where cntrltype = 0
                            and substring (phyname, 1, charindex ('t', phyname) -1)  =
                                substring (mirrorname, 1, charindex ('t', mirrorname) -1)))
            begin
               print '    THERE ARE MIRRORED DEVICES ON THE SAME CONTROLLER.'

               print ' '

               select 'DEVICE' = substring (name, 1, 25),
                      PHYSICAL = substring (phyname, 1, 25),
                      'MIRROR' = substring (mirrorname, 1, 25)
               from master.dbo.sysdevices
               where cntrltype = 0
                 and substring (phyname, 1, charindex ('t', phyname) -1)
                     = substring (mirrorname, 1, charindex ('t', mirrorname) -1)
               order by name
            end
         else
            begin
               print '    ALL MIRRORED DEVICES ARE ON DIFFERENT CONTROLLERS.'
            end

         print ' '

         if (exists (select *
                     from master.dbo.sysdevices
                     where cntrltype = 0
                       and (((status > 31)  and  (status & 512 <> 512))  or
                            ((status < 32)  and  ((status > 3)  or  (mirrorname IS NOT NULL))))))
            begin
               print '    AT LEAST ONE DATABASE/DEVICE HAS AN INCORRECT STATUS.'

               print ' '

               select 'DISABLED MIRROR' = substring (dv.name, 1, 16),
                      STATUSES          = substring (vV.name, 1, 16),
                      PHYSICAL          = substring (dv.phyname, 1, 21),
                      'MIRROR'          = substring (dv.mirrorname, 1, 21)
               from master.dbo.sysdevices dv,
                    sybsystemprocs.dbo.usptvalues vV
               where dv.cntrltype = 0
                 and vV.type      = 'V'
                 and vV.number    = (dv.status & vV.number)
                 and (((dv.status > 31)  and  (dv.status & 512 <> 512))  or
                      ((dv.status < 32)  and  ((dv.status > 3)  or  (dv.mirrorname IS NOT NULL))))
               order by dv.name, vV.number
            end
         else
            begin
               print '    ALL DATABASES/DEVICES ARE MIRRORED/UNMIRRORED CONSISTENTLY.'
            end

         print ' '

         if (1 < any (select count (distinct (dv.status - (dv.status % 2)))
                      from master.dbo.sysdevices dv,
                           master.dbo.sysusages  u
                      where dv.cntrltype  = 0
                        and dv.low       <= u.vstart + u.size
                        and dv.high      >= u.vstart + u.size - 1
                      group by u.dbid, u.segmap
                      having dv.cntrltype  = 0
                        and dv.low        <= u.vstart + u.size
                        and dv.high       >= u.vstart + u.size - 1))
            begin
               print '    AT LEAST ONE DATABASE/LOG IS SPLIT ON UNLIKE DEVICES.'

               print ' '

               insert into #mrdv
                  select dv.low, dv.high, substring (dv.name, 1, 15), max (vV.number),
                         NULL, dv.status, 0, 0
                  from master.dbo.sysdevices dv,
                       sybsystemprocs.dbo.usptvalues vV
                  where dv.cntrltype = 0
                    and vV.type      = 'V'
                    and vV.number   in (2, 64, 128, 256, 512)
                    and vV.number    = (dv.status & vV.number)
                  group by dv.name
                  having dv.cntrltype = 0
                     and vV.type      = 'V'
                     and vV.number   in (2, 64, 128, 256, 512)
                     and vV.number    = (dv.status & vV.number)

               insert into #mru
                  select u.dbid,
                         u.segmap,
                         u.size,
                         u.vstart,
                         db_name (u.dbid),
                         substring (vS.name, 1, 12),
                         substring (convert (char (17), convert (money, u.size), 1), 1, 14)
                  from master.dbo.sysusages u,
                       sybsystemprocs.dbo.usptvalues vS
                  where vS.type   = 'S'
                    and vS.number = (u.segmap & 7)
                    and 1         < (select count (distinct (dv2.status - (dv2.status % 2)))
                                     from master.dbo.sysdevices dv2,
                                          master.dbo.sysusages  u2
                                     where dv2.cntrltype    = 0
                                       and u2.dbid          = u.dbid
                                       and (u2.segmap & 7)  = (u.segmap & 7)
                                       and dv2.low         <= u2.vstart + u2.size
                                       and dv2.high        >= u2.vstart + u2.size -1)

               select distinct #mru.dbname,
                               #mru.usage,
                               #mrdv.sybname,
                               #mrdv.status,
                               statustext = vV.name,
                               #mru.pages,
                               sort = #mru.dbname  +  ' '  +  vV.name  +  #mrdv.sybname
               into #drunlike
               from #mru,
                    #mrdv,
                    sybsystemprocs.dbo.usptvalues vV
               where vV.type     = 'V'
                 and vV.number   = #mrdv.number
                 and #mrdv.low  <= #mru.vstart + #mru.size
                 and #mrdv.high >= #mru.vstart + #mru.size - 1

               update #drunlike
               set statustext = substring ('serial '  +  statustext, 1, 17)
               where (status & 32) = 32

               insert into #drunlike
                  select ' ', ' ', ' ', 0, ' ', ' ', min (sort)
                  from #drunlike
                  group by dbname

               delete from #drunlike
               where dbname  = ' '
                 and sybname = ' '
                 and sort    = (select min (sort)
                                from #drunlike)

               update #drunlike
               set dbname  = ' '
               from #drunlike dr1
               where exists (select *
                             from #drunlike dr2
                             where dr2.dbname = dr1.dbname
                               and dr2.sort   = dr1.sort
                               and dr2.sort   > (select min (dr3.sort)
                                                 from #drunlike dr3
                                                 where dr3.dbname = dr2.dbname))

               select distinct 'SPLIT MIRRORED DB' = substring (dbname, 1, 17),
                               USAGE               = usage,
                               'SYBASE DEVICE'     = substring (sybname, 1, 13),
                               STATUS              = substring (statustext, 1, 17),
                               'FRAGMENT PAGES'    = pages
               from #drunlike
               order by sort
            end
         else
            begin
               print '    NO DATABASE/LOG IS SPLIT ON MIRRORED/UNMIRRORED DEVICES.'
            end
      end

   print ' '

   truncate table #mrdv

   truncate table #mru

   insert into #mrdv
      select low, high, substring (name, 1, 15), 0, phyname, status, 0, 0
      from master.dbo.sysdevices
      where cntrltype = 0

   while (exists (select *
                  from #mrdv
                  where charindex ('/', phyname) > 0))
      begin
         update #mrdv
         set phyname = substring (phyname, charindex ('/', phyname) + 1, 127)
         where charindex ('/', phyname) > 0
      end

   update #mrdv
   set C = -1,
       T = -1
   where phyname = 'd_master'

   update #mrdv
   set C = convert (tinyint,
                    substring (phyname,
                               charindex ('c', phyname) + 1,
                               charindex ('t', phyname) - charindex ('c', phyname) - 1))
   where charindex ('c', phyname) > 0
     and charindex ('t', phyname) > 0

   update #mrdv
   set T = convert (tinyint,
                    substring (phyname,
                               charindex ('t', phyname) + 1,
                               charindex ('d', phyname) - charindex ('t', phyname) - 1))
   where charindex ('t', phyname)  > 0
     and charindex ('d', phyname)  > 0
     and phyname                  <> 'd_master'

   insert into #mru
      select u.dbid,
             (u.segmap & 7),
             u.size,
             u.vstart,
             db_name (u.dbid),
             substring (vS.name, 1, 12),
             substring (convert (char (17), convert (money, u.size), 1), 1, 14)
      from master.dbo.sysusages u,
           sybsystemprocs.dbo.usptvalues vS
      where vS.type   = 'S'
        and vS.number = (u.segmap & 7)

   update #mru
   set segmap = 0
   where segmap in (1, 2, 3, 5, 6)

   select distinct u.dbname,
                   dv.sybname,
                   dv.phyname,
                   u.vstart,
                   u.usage,
                   u.pages,
                   sort = u.dbname  +  ' '  +  dv.sybname
   into #dr
   from #mru  u,
        #mrdv dv
   where dv.low  <= u.vstart + u.size
     and dv.high >= u.vstart + u.size - 1
     and exists (select u2.dbid
                 from #mru  u2,
                      #mrdv dv2
                 where u2.dbid    = u.dbid
                   and dv2.low   <= u2.vstart + u2.size
                   and dv2.high  >= u2.vstart + u2.size - 1
                   and dv2.C      = dv.C
                   and dv2.T      = dv.T
                   and u2.segmap <> u.segmap)

   if (exists (select *
               from #dr))
      begin
         print '    THERE ARE "SEPARATED" DATABASES/LOGS ON THE SAME DISK.'

         print ' '

         update #dr
         set dbname  = ' ',
             sybname = ' ',
             phyname = ' '
         from #dr dr1
         where exists (select *
                       from #dr dr2
                       where dr2.sort   = dr1.sort
                         and dr2.vstart = dr1.vstart
                         and dr2.vstart > (select min (dr3.vstart)
                                           from #dr dr3
                                           where dr3.sort = dr2.sort))

         update #dr
         set dbname = ' '
         from #dr dr1
         where dr1.dbname <> ' '
           and exists (select *
                       from #dr dr2
                       where dr2.dbname  = dr1.dbname
                         and dr2.sybname = dr1.sybname
                         and dr2.sybname > (select min (dr3.sybname)
                                            from #dr dr3
                                            where dr3.dbname = dr2.dbname))

         insert into #dr
            select ' ', ' ', ' ', 0, ' ', ' ', min (sort)
            from #dr
            where dbname <> ' '
            group by sort
            having dbname <> ' '

         delete from #dr
         where dbname  = ' '
           and sybname = ' '
           and vstart  = 0
           and sort    = (select min (sort)
                          from #dr)

         select 'DATABASE'       = substring (dbname, 1, 21),
                'SYBASE DEVICE'  = substring (sybname, 1, 15),
                'UNIX DEVICE'    = substring (phyname, 1, 11),
                USAGE            = usage,
                'FRAGMENT PAGES' = pages
         from #dr
         order by sort, vstart
      end
   else
      begin
         print '    ALL "SEPARATED" DATABASES/LOGS ARE ON DIFFERENT DISKS.'
      end

   print ' '
go
sp_addcomment sp_deviceright, 11, 'Checks for device (mirror & database/log) inconsistencies'
go
sp_procxmode sp_deviceright, anymode
go

------------ Temporary tables needed for sp_domainsright subroutines ---------

create table #dr23
   (id        int           not null,
    uid       smallint      not null,
    TC        varchar (60)  not null,
    colid     tinyint       not null,
    cnstrid   int           not null,
    cnstrtxt  varchar (60)  not null,
    cnstrtype varchar (11)  not null,
    sorttext  varchar (60)  not null,
    beginas   tinyint       not null)
go

create table #dr45
   (id         int           not null,
    uid        smallint      not null,
    TC         varchar (60)  not null,
    colid      tinyint       not null,
    cnstrid    int           not null,
    cnstrtxt   varchar (60)  not null,
    cnstrtype  varchar (11)  not null,
    utid       int           not null,
    uttxt      varchar (60)  not null,
    utdfltid   int           not null,
    utdflttxt  varchar (60)  not null,
    sorttxt    varchar (60)  not null,
    beginas    tinyint       not null)
go

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

   declare @CR         char (1),
           @HT         char (1),
           @LF         char (1),
           @maxtext    tinyint

   select @CR = char (13),                            -- Carriage Return
          @HT = char (9),                             -- Horizontal Tab
          @LF = char (10)                             -- Line Feed

   update #dr23
   set TC = substring (TC + '.' + col_name (id, colid), 1, 60)

   update #dr23
   set TC = substring (TC + ' (' + user_name (o.uid) + ')', 1, 60)
   from #dr23 d,
        sysobjects o
   where o.id = d.id
     and ((o.uid <> 1)  or
          (1      < (select count (o2.uid)
                     from sysobjects o2
                     where o.name            like o2.name + '%'
                       and (o2.sysstat & 15)    = 7)))

   update #dr23
   set sorttext = TC

   update #dr23
   set cnstrtxt = substring (c.text, 1, 60)
   from #dr23 d,
        syscomments c
   where d.cnstrtype  = 'check'
     and c.id       = d.cnstrid
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   update #dr23
   set cnstrtxt = substring (c.text, charindex ('(@', text), 60)
   from #dr23 d,
        syscomments c
   where d.cnstrtype            = 'rule'
     and c.id                   = d.cnstrid
     and c.texttype             = 0
     and charindex ('(@', text) > 0

   update #dr23
   set cnstrtxt = substring (c.text, charindex ('@', text), 60)
   from #dr23 d,
        syscomments c
   where d.cnstrtype           = 'rule'
     and d.cnstrtxt            = ' '
     and c.id                  = d.cnstrid
     and c.texttype            = 0
     and charindex ('@', text) > 0

   update #dr23
   set cnstrtxt = ltrim (substring (cnstrtxt, charindex ('(', cnstrtxt) + 1, 60))
   where cnstrtype = 'check'

   while (exists (select *
                  from #dr23
                  where charindex (@CR, cnstrtxt) > 0))
      begin
         update #dr23
         set cnstrtxt = stuff (cnstrtxt, charindex (@CR, cnstrtxt), 1, ' ')
         where charindex (@CR, cnstrtxt) > 0
      end

   while (exists (select *
                  from #dr23
                  where charindex (@HT, cnstrtxt) > 0))
      begin
         update #dr23
         set cnstrtxt = stuff (cnstrtxt, charindex (@HT, cnstrtxt), 1, ' ')
         where charindex (@HT, cnstrtxt) > 0
      end

   while (exists (select *
                  from #dr23
                  where charindex (@LF, cnstrtxt) > 0))
      begin
         update #dr23
         set cnstrtxt = stuff (cnstrtxt, charindex (@LF, cnstrtxt), 1, ' ')
         where charindex (@LF, cnstrtxt) > 0
      end

   update #dr23
   set beginas = 4 + charindex (' as ', lower (cnstrtxt))
   where cnstrtype = 'rule'

   update #dr23
   set cnstrtxt = ltrim (substring (cnstrtxt, isnull (beginas, 1), 60))
   where cnstrtype = 'rule'

   update #dr23
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 1)
   where cnstrtype   = 'check'
     and cnstrtxt like '%)'

   update #dr23
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 2)
   where cnstrtype   = 'check'
     and cnstrtxt like '%),'

   select @maxtext = max (char_length (TC))
   from #dr23

   update #dr23
   set TC = ' '
   from #dr23 d1
   where exists (select *
                 from #dr23 d2
                 where d2.id        = d1.id
                   and d2.colid     = d1.colid
                   and d2.cnstrtype = d1.cnstrtype
                   and d2.cnstrtxt  = d1.cnstrtxt
                   and (d2.cnstrtype + '  ' + d2.cnstrtxt) > (select min (d3.cnstrtype + '  ' + d3.cnstrtxt)
                                                              from #dr23 d3
                                                              where d3.id    = d2.id
                                                                and d3.colid = d2.colid))

   if (@maxtext < 26)
      begin
         select 'TABLE.COLUMN' = substring (TC, 1, 25),
                TYPE           = cnstrtype,
                DETAIL         = substring (cnstrtxt, 1, 39)
         from #dr23
         order by sorttext, colid, cnstrtype, cnstrtxt
      end
   else if (@maxtext < 31)
      begin
         select 'TABLE.COLUMN' = substring (TC, 1, 30),
                TYPE           = cnstrtype,
                DETAIL         = substring (cnstrtxt, 1, 34)
         from #dr23
         order by sorttext, colid, cnstrtype, cnstrtxt
      end
   else
      begin
         select 'TABLE.COLUMN' = substring (TC, 1, 35),
                TYPE           = cnstrtype,
                DETAIL         = substring (cnstrtxt, 1, 29)
         from #dr23
         order by sorttext, colid, cnstrtype, cnstrtxt
      end
go
sp_procxmode sp_domainsright_col_cnstrnts, unchained
go

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

   declare @CR         char (1),
           @HT         char (1),
           @LF         char (1)

   select @CR = char (13),                            -- Carriage Return
          @HT = char (9),                             -- Horizontal Tab
          @LF = char (10)                             -- Line Feed

   update #dr45
   set TC = substring (d.TC + ' (' + user_name (d.uid) + ')', 1, 60)
   from #dr45 d,
        sysobjects o
   where o.id = d.id
     and ((d.uid <> 1)  or
          (1       < (select count (o2.uid)
                                    from sysobjects o2
                                    where o2.name            = object_name (o.id)
                                      and (o2.sysstat & 15) in (2, 3))))

   update #dr45
   set utdflttxt = substring (c.text, 1, 60)
   from #dr45 d,
        syscomments c
   where c.id       = d.utdfltid
     and c.colid    = 1
     and c.colid2   = 0
     and c.texttype = 0

   while (exists (select *
                  from #dr45
                  where charindex (@CR, utdflttxt) > 0))
      begin
         update #dr45
         set utdflttxt = stuff (utdflttxt, charindex (@CR, utdflttxt), 1, ' ')
         where charindex (@CR, utdflttxt) > 0
      end

   while (exists (select *
                  from #dr45
                  where charindex (@HT, utdflttxt) > 0))
      begin
         update #dr45
         set utdflttxt = stuff (utdflttxt, charindex (@LF, utdflttxt), 1, ' ')
         where charindex (@HT, utdflttxt) > 0
      end

   while (exists (select *
                  from #dr45
                  where charindex (@LF, utdflttxt) > 0))
      begin
         update #dr45
         set utdflttxt = stuff (utdflttxt, charindex (@LF, utdflttxt), 1, ' ')
         where charindex (@LF, utdflttxt) > 0
      end

   update #dr45
   set beginas = 4 + charindex (' as ', lower (utdflttxt))

   update #dr45
   set utdflttxt = ltrim (substring (utdflttxt, isnull (beginas, 1), 60))

   update #dr45
   set sorttxt = TC

   update #dr45
   set TC = ' '
   from #dr45 d1
   where exists (select *
                 from #dr45 d2
                 where d2.TC       = d1.TC
                   and d2.cnstrtxt = d1.cnstrtxt
                   and d2.cnstrtxt > (select min (d3.cnstrtxt)
                                      from #dr45 d3
                                      where d3.TC = d2.TC))

   select 'TABLE.COLUMN'  = substring (TC, 1, 28),
          'RULE / CHECK'  = substring (cnstrtxt, 1, 17),
          USERTYPE        = substring (uttxt, 1, 12),
          'USERTYPE RULE' = substring (utdflttxt, 1, 17)
   from #dr45
   order by sorttxt, cnstrtxt
go
sp_procxmode sp_domainsright_col_ut_rules, unchained
go

drop table #dr23
go
drop table #dr45
go

create procedure sp_domainsright as

   --   SOME OF THE WORK TABLES ARE USED IN MULTIPLE SECTIONS AND THE COLUMN
   --   NAMES CAN BE MISLEADING.  THE COMMENTS FOR THE TABLE DECLARATIONS
   --   ARE ONLY GUARANTEED FOR THE INITIAL TABLE USAGE.

   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_domainsright> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @CR         char (1),
           @HT         char (1),
           @LF         char (1),
           @maxtext    tinyint

   select @CR = char (13),                            -- Carriage Return
          @HT = char (9),                             -- Horizontal Tab
          @LF = char (10)                             -- Line Feed

   create table #dr1
      (id     int           not null,      -- object id
       uid    smallint      not null,      -- object uid
       itext  varchar (53)  not null,      -- identifier text
       itype  varchar (23)  not null)      -- identifier type

   if (user_id () = 1)
      begin
         print '    UPDATING STATISTICS ON SYSTEM TABLES.'

         print ' '

         update statistics syscomments

         update statistics sysobjects
      end

   --  SEARCH FOR IMPROPER QUOTED IDENTIFIERS.

   insert into #dr1
      select distinct o.id, o.uid, o.name, vO.name
      from sysobjects o,
           sybsystemprocs.dbo.usptvalues vO
      where ((valid_name (o.name) = 0)  or  (charindex ('"', o.name) > 0))
        and vO.number  = convert (int, o.sysstat & 15)
        and vO.type    = 'O'
        and vO.number in (4, 6, 8)

   insert into #dr1
      select o.id, o.uid, i.name, 'index'
      from sysindexes i,
           sysobjects o
      where i.indid > 0
        and i.indid < 254
        and ((valid_name (i.name) = 0)  or  (charindex ('"', i.name) > 0))
        and o.id    = i.id

   insert into #dr1
      select o.id, o.uid, o.name, 'rule'
      from sysobjects o,
           sysprocedures p
      where ((valid_name (o.name) = 0)  or  (charindex ('"', o.name) > 0))
        and (o.sysstat & 15)   = 7
        and p.id               = o.id
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   insert into #dr1
      select 0, 1, name, 'segment'
      from syssegments
      where ((valid_name (name) = 0)  or  (charindex ('"', name) > 0))
        and name <> 'default'

   insert into #dr1
      select 0, uid, name, 'usertype'
      from systypes
      where valid_name (name)     = 0
         or charindex ('"', name) > 0

   update #dr1
   set itext = substring (itext + ' (' + user_name (o.uid) + ')', 1, 53)
   from #dr1 d,
        sysobjects o
   where o.id = d.id
     and ((o.uid <> 1)  or
          (1      < (select count (o2.uid)
                     from sysobjects o2
                     where o.name like o2.name + '%')))

   update #dr1
   set itext = substring (object_name (id) + '.' + itext, 1, 53)
   where itype = 'index'

   if (db_id () = 1)
      begin
         insert into #dr1
            select 0, 0, name, 'character set'
            from master.dbo.syscharsets
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'database'
            from master.dbo.sysdatabases
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'device'
            from master.dbo.sysdevices
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'language'
            from master.dbo.syslanguages
            where valid_name (name)     = 0
               or charindex ('"', name) > 0

         insert into #dr1
            select 0, 0, name, 'login'
            from master.dbo.syslogins
            where valid_name (name)     = 0
               or charindex ('"', name) > 0
      end
   else
      begin
         insert into #dr1
            select 0, 0, db_name (), 'database'
            where valid_name (db_name())     = 0
               or charindex ('"', db_name()) > 0
      end

   if (exists (select *
               from #dr1))
      begin
         print '    THERE ARE INVALID QUOTED IDENTIFIERS.'

         print ' '

         select @maxtext = max (char_length (itext))
         from #dr1

         if (@maxtext < 26)
            begin
               select IDENTIFIER        = substring (itext, 1, 25),
                      'IDENTIFIER TYPE' = itype
               from #dr1
               order by itext, itype
            end
         else if (@maxtext < 41)
            begin
               select IDENTIFIER        = substring (itext, 1, 40),
                      'IDENTIFIER TYPE' = itype
               from #dr1
               order by itext, itype
            end
         else
            begin
               select IDENTIFIER        = itext,
                      'IDENTIFIER TYPE' = itype
               from #dr1
               order by itext, itype
            end
      end
   else
      begin
         print '    ALL QUOTED IDENTIFIERS ARE FOR VALID OBJECTS.'
      end

   drop table #dr1

   print ' '

   --  SEARCH FOR COLUMNS WITH MULTIPLE CONTRAINTS (INCLUDING SINGULAR
   --  FOREIGN KEY CONSTRAINTS)

   create table #dr23
      (id        int           not null,   -- table id
       uid       smallint      not null,   -- table uid
       TC        varchar (60)  not null,   -- table.column text
       colid     tinyint       not null,   -- column id
       cnstrid   int           not null,   -- constraint id
       cnstrtxt  varchar (60)  not null,   -- constraint text
       cnstrtype varchar (11)  not null,   -- constraint type
       sorttext  varchar (60)  not null,   -- since TC may be blanked out ...
       beginas   tinyint       null)       -- where does ' as ' begin?

   insert into #dr23
      select o.id, o.uid, o.name, c.colid, c.constrid, ' ', 'check', ' ', NULL
      from sysconstraints c,
           sysobjects o
      where (c.status & 128) = 128
        and o.id             = c.tableid
        and c.colid          > 0

   insert into #dr23
      select o.id, o.uid, o.name, c.colid, c.domain, ' ', 'rule', ' ', NULL
      from syscolumns c,
           sysobjects o,
           sysprocedures p
      where (p.status & 4096) <> 4096
        and p.sequence         = 1
        and c.domain           = p.id
        and o.id               = c.id

   insert into #dr23
      select o.id,
             o.uid,
             o.name,
             r.fokey1,
             r.constrid,
             substring (object_name (r.reftabid, r.pmrydbid)
                        + ' ('
                        + col_name (r.reftabid, r.refkey1, r.pmrydbid)
                        + ')',
                        1,
                        60),
             'foreign key',
             ' ',
             NULL
      from sysreferences r,
           sysobjects o
      where r.fokey2 = 0
        and o.id     = r.tableid

   delete #dr23
   from #dr23 d
   where 1 = (select count (dx.id)
              from #dr23 dx
              where d.id    = dx.id
                and d.colid = dx.colid)

   if (exists (select *
               from #dr23))
      begin
         print '    THERE ARE COLUMNS WITH CHECK/RULE/SINGULAR FOREIGN KEY CONSTRAINTS.'

         print ' '

         execute sp_domainsright_col_cnstrnts
      end
   else
      begin
         print '    NO COLUMN HAS CHECK/RULE/SINGULAR FOREIGN KEY CONSTRAINTS.'
      end

   print ' '

   delete from #dr23

   --  SEARCH FOR COLUMN CHECKS WHICH DO NOT REFERENCE THE COLUMN.  THIS
   --  SEARCH IS FAR FROM FOOLPROOF.

   if (exists (select o.*
               from syscolumns c,
                    syscomments cmc,
                    sysconstraints cn,
                    sysobjects o
               where o.id                         = cn.constrid
                 and (o.sysstat & 15)             = 7
                 and c.id                         = cn.tableid
                 and c.colid                      = cn.colid
                 and cmc.id                       = cn.constrid
                 and ((cmc.text like 'CHECK %')  or  (cmc.text like 'CONSTRAINT %'))
                 and charindex (c.name, cmc.text) = 0))
      begin
         print '    THERE ARE COLUMN CHECKS NOT REFERENCING THE COLUMN.'

         print ' '

         insert into #dr23
            select distinct 0,
                            0,
                            substring (object_name (c.id) + '.' + c.name, 1, 60),
                            0,
                            0,
                            substring (substring (cmc.text,
                                                  1 + charindex ('(', cmc.text),
                                                  char_length (cmc.text) - charindex ('(', cmc.text) - 1),
                                       1,
                                       60),
                            ' ',
                            ' ',
                            NULL
            from syscolumns c,
                 syscomments cmc,
                 sysconstraints cn,
                 sysobjects o
            where (o.sysstat & 15)             = 7
              and cn.colid                     > 0
              and c.id                         = cn.tableid
              and c.colid                      = cn.colid
              and cmc.id                       = cn.constrid
              and ((cmc.text like 'CHECK %')  or  (cmc.text like 'CONSTRAINT %'))
              and charindex (c.name, cmc.text) = 0

         update #dr23
         set sorttext = TC

         update #dr23
         set TC = ' '
         from #dr23 d1
         where exists (select *
                       from #dr23 d2
                       where d2.TC       = d1.TC
                         and d2.cnstrtxt = d1.cnstrtxt
                         and d2.cnstrtxt > (select min (d3.cnstrtxt)
                                            from #dr23 d3
                                            where d3.TC    = d2.TC))

         select 'TABLE.COLUMN' = substring (TC, 1, 25),
                'CHECK'        = substring (cnstrtxt, 1, 51)
         from #dr23
         order by sorttext, cnstrtxt
      end
   else
      begin
         print '    ALL COLUMN CONTRAINTS HAVE A REFERENCE TO THE COLUMN.'
      end

   drop table #dr23

   print ' '

   --  SEARCH FOR COLUMNS WITH OVERRIDES OF USERTYPE DEFAULTS.

   create table #dr45
      (id         int           not null,   -- table id
       uid        smallint      not null,   -- table uid
       TC         varchar (60)  not null,   -- table.column text
       colid      tinyint       not null,   -- column id
       cnstrid    int           not null,   -- column constraint id
       cnstrtxt   varchar (60)  not null,   -- column constraint text
       cnstrtype  varchar (11)  not null,   -- literal/usertype
       utid       int           not null,   -- usertype
       uttxt      varchar (60)  not null,   -- usertype text
       utdfltid   int           not null,   -- usertype default id
       utdflttxt  varchar (60)  not null,   -- usertype default text
       sorttxt    varchar (60)  not null,
       beginas    tinyint       null)       -- where does ' as ' begin?

   insert into #dr45                                  -- literal defaults
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.cdefault,
             ltrim (substring (cm.text, 8, 60)),
             'literal',
             t.usertype,
             t.name,
             t.tdefault,
             object_name (t.tdefault),
             ' ',
             NULL
      from syscolumns c,
           syscomments cm,
           sysobjects o,
           sysprocedures p,
           systypes t
      where t.usertype         > 99
        and t.tdefault        <> 0
        and t.usertype         = c.usertype
        and t.tdefault        <> c.cdefault
        and c.id               = o.id
        and (o.sysstat & 15)   = 3
        and cm.id              = c.cdefault
        and cm.colid           = 1
        and cm.texttype        = 0
        and p.id               = c.cdefault
        and p.sequence         = 1
        and (p.status & 4096)  = 4096

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, charindex (@CR, cnstrtxt) - 1)
   where charindex (@CR, cnstrtxt) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, charindex (@LF, cnstrtxt) - 1)
   where charindex (@LF, cnstrtxt) > 0

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 2, char_length (cnstrtxt) - 2)
   where char_length (cnstrtxt) > 2

   insert into #dr45                                  -- named defaults
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.cdefault,
             object_name (c.cdefault),
             'usertype',
             t.usertype,
             t.name,
             t.tdefault,
             object_name (t.tdefault),
             ' ',
             NULL
      from syscolumns c,
           sysobjects o,
           sysprocedures p,
           systypes t
      where t.usertype         > 99
        and t.tdefault        <> 0
        and t.usertype         = c.usertype
        and t.tdefault        <> c.cdefault
        and c.id               = o.id
        and (o.sysstat & 15)   = 3
        and p.id               = c.cdefault
        and p.sequence         = 1
        and (p.status & 4096) <> 4096

   update #dr45
   set TC = substring (d.TC + ' (' + user_name (d.uid) + ')', 1, 60)
   from #dr45 d,
        sysobjects o
   where o.id = d.id
     and ((d.uid <> 1)  or
          (1      < (select count (o2.uid)
                                   from sysobjects o2
                                   where o2.name            = object_name (o.id)
                                     and (o2.sysstat & 15) in (2, 3))))

   update #dr45
   set cnstrtxt = substring (d.cnstrtxt + ' (' + user_name (o.uid) + ')', 1, 60)
   from #dr45 d,
        sysobjects o
   where d.cnstrtype  = 'usertype'
     and o.id         = d.cnstrid
     and o.uid       <> d.uid

   update #dr45
   set utdflttxt = substring (d.utdflttxt + ' (' + user_name (t.uid) + ')', 1, 60)
   from #dr45 d,
        systypes t
   where t.usertype  = d.utid
     and t.uid      <> d.uid

   if (exists (select *
               from #dr45))
      begin
         if (1 < (select count (*)
                  from #dr45))
            begin
               print '    USERTYPE DEFAULTS HAVE BEEN OVERRIDEN.'
            end
         else
            begin
               print '    A USERTYPE DEFAULT HAS BEEN OVERRIDEN.'
            end

         print ' '

         update #dr45
         set sorttxt = TC

         select 'TABLE.COLUMN'     = substring (TC, 1, 28),
                'DEFAULT'          = substring (cnstrtxt, 1, 17),
                USERTYPE           = substring (uttxt, 1, 12),
                'USERTYPE DEFAULT' = substring (utdflttxt, 1, 17)
         from #dr45
         order by TC
      end
   else
      begin
         print '    NO USERTYPE DEFAULT HAS BEEN OVERRIDEN.'
      end

   print ' '

   delete from #dr45

   --  SEARCH FOR COLUMNS WITH ADDITIONS/OVERRIDES OF USERTYPE RULES.

   insert into #dr45                                  -- rules
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.domain,
             cm.text,
             'rule',
             t.usertype,
             t.name,
             t.domain,
             ' ',
             ' ',
             NULL
      from syscolumns c,
           syscomments cm,
           sysobjects o,
           sysprocedures p,
           systypes t
      where t.usertype         > 99
        and t.domain          <> 0
        and c.usertype         = t.usertype
        and c.domain          <> t.domain
        and o.id               = c.id
        and (o.sysstat & 15)   = 3
        and p.id               = c.domain
        and p.sequence         = 1
        and (p.status & 4096) <> 4096
        and cm.id              = c.domain
        and cm.colid           = 1
        and cm.colid2          = 0
        and cm.texttype        = 0

   while (exists (select *
                  from #dr45
                  where charindex (@CR, cnstrtxt) > 0))
      begin
         update #dr45
         set cnstrtxt = stuff (cnstrtxt, charindex (@CR, cnstrtxt), 1, ' ')
         where charindex (@CR, cnstrtxt) > 0
      end

   while (exists (select *
                  from #dr45
                  where charindex (@HT, cnstrtxt) > 0))
      begin
         update #dr45
         set cnstrtxt = stuff (cnstrtxt, charindex (@HT, cnstrtxt), 1, ' ')
         where charindex (@HT, cnstrtxt) > 0
      end

   while (exists (select *
                  from #dr45
                  where charindex (@LF, cnstrtxt) > 0))
      begin
         update #dr45
         set cnstrtxt = stuff (cnstrtxt, charindex (@LF, cnstrtxt), 1, ' ')
         where charindex (@LF, cnstrtxt) > 0
      end

   update #dr45
   set beginas = 4 + charindex (' as ', lower (cnstrtxt))

   update #dr45
   set cnstrtxt = ltrim (substring (cnstrtxt, isnull (beginas, 1), 60))

   insert into #dr45                                  -- checks
      select o.id,
             o.uid,
             o.name + '.' + c.name,
             c.colid,
             c.domain,
             cm.text,
             'check',
             t.usertype,
             t.name,
             t.domain,
             ' ',
             ' ',
             NULL
      from syscolumns c,
           syscomments cm,
           sysconstraints cs,
           sysobjects o,
           systypes t
      where t.usertype        > 99
        and t.domain         <> 0
        and c.usertype        = t.usertype
        and c.domain         <> t.domain
        and o.id              = c.id
        and (o.sysstat & 15)  = 3
        and cs.tableid        = c.id
        and cs.colid          = c.colid
        and (cs.status & 128) = 128
        and cm.id             = cs.constrid
        and cm.colid          = 1
        and cm.colid2         = 0
        and cm.texttype       = 0

   update #dr45
   set cnstrtxt = ltrim (substring (cnstrtxt, charindex ('(', cnstrtxt) + 1, 60))
   where cnstrtype = 'check'

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 1)
   where cnstrtype   = 'check'
     and cnstrtxt like '%)'

   update #dr45
   set cnstrtxt = substring (cnstrtxt, 1, char_length (cnstrtxt) - 2)
   where cnstrtype   = 'check'
     and cnstrtxt like '%),'

   if (exists (select *
               from #dr45))
      begin
         if (1 < (select count (*)
                  from #dr45))
            begin
               print '    USERTYPE RULES HAVE BEEN OVERRIDEN OR APPENDED TO.'
            end
         else
            begin
               print '    A USERTYPE RULE HAS BEEN OVERRIDEN OR APPENDED TO.'
            end

         print ' '

         execute sp_domainsright_col_ut_rules
      end
   else
      begin
         print '    NO USERTYPE RULE HAS BEEN OVERRIDEN.'
      end

   print ' '

   drop table #dr45

   if (exists (select *
               from syscolumns
               where type    = 50
                 and domain <> 0))
      begin
         print '    THERE ARE BIT COLUMNS WITH A CHECK OR RULE.'

         print ' '

         create table #dr6
            (id        int           not null,
             uid       smallint      not null,
             tabletext varchar (63)  not null,
             coltext   varchar (30)  not null,
             colid     tinyint       not null,
             domain    int           not null)

         insert into #dr6
            select o.id, o.uid, o.name, c.name, c.colid, c.domain
            from syscolumns c,
                 sysobjects o
            where c.type    = 50
              and c.domain <> 0
              and o.id      = c.id

         update #dr6
         set tabletext = d.tabletext  +  ' ('  +  user_name (d.uid)  +  ')'
         from #dr6 d
         where d.uid <> 1
            or 1      < (select count (o.uid)
                         from sysobjects o
                         where o.name           = d.tabletext
                           and (o.sysstat & 15) = 3)

         select 'TABLE'      = substring (tabletext, 1, 25),
                'COLUMN'     = substring (coltext, 1, 25),
                'CHECK/RULE' = substring (object_name (domain), 1, 25)
         from #dr6
         order by object_name (id), user_name (uid), colid
      end
   else
      begin
         print '    NO BIT COLUMNS HAVE A CHECK OR RULE.'
      end

   print ' '
go
sp_addcomment sp_domainsright, 11, 'Checks for consistency of column constraints/defaults'
go
sp_procxmode sp_domainsright, anymode
go

------------ Temporary tables needed for creation of sp_groupsright subroutines

create table #groupsright
   (db        varchar (30)  not null,
    suid      smallint      not null,
    username  varchar (30)  not null,
    groupname varchar (30)  not null)
go

create table #grprght2
   (groupname varchar (30)  not null,
    db        varchar (30)  not null,
    username  varchar (30)  not null,
    sysname   varchar (30)  null,
    sort      varchar (92)  not null)
go

create procedure sp_groupsright_load  (@GROUP varchar (30))  as
   set ansinull                      on
   set string_rtruncation            on

   insert into #groupsright
      select db_name (), uu.suid, uu.name, ug.name
      from sysusers ug, sysusers uu
      where ug.name = isnull (@GROUP, ug.name)
        and ug.uid  = uu.gid
        and uu.uid <> uu.gid
        and ug.uid  = ug.gid
        and ug.uid <> 0
        and not exists (select r.lrid
                        from sysroles r
                        where r.lrid = ug.gid)

   insert into #groupsright
      select db_name (), -1, '(none)', ug.name
      from sysusers ug
      where ug.name = isnull (@GROUP, ug.name)
        and ug.uid  = ug.gid
        and not exists (select r.lrid
                        from sysroles r
                        where r.lrid = ug.gid)
        and not exists (select uu.*
                        from sysusers uu
                        where uu.uid <> uu.gid
                          and ug.uid  = uu.gid)
go
sp_procxmode sp_groupsright_load, unchained
go

create procedure sp_groupsright_print (@GROUP varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @groupcnt  varchar (7)

   if (exists (select *
               from #groupsright
               where not exists (select #grprght2.*
                                 from #grprght2
                                 where #grprght2.groupname = #groupsright.groupname)))
      begin
         select distinct 'CONSISTENT SERVER GROUPS' = #groupsright.groupname
         from #groupsright
         where not exists (select #grprght2.*
                           from #grprght2
                           where #grprght2.groupname = #groupsright.groupname)
         order by #groupsright.groupname

         print ' '
      end

   if (exists (select *
               from #grprght2))
      begin
         select @groupcnt = ltrim (substring (convert (char (10),
                                                       convert (money,
                                                                count (distinct groupname)),
                                                                1),
                                              1,
                                              7))
         from #grprght2

         if (@GROUP IS NULL)
            begin
               if (@groupcnt = '1')
                  begin
                     print '    THERE IS ONE INCONSISTENT GROUP.'
                  end
               else
                  begin
                     print '    THERE ARE %1! INCONSISTENT GROUPS.', @groupcnt
                  end

               print ' '
            end
         else if (@groupcnt = '1')
            begin
               print '    GROUP <%1!> IS INCONSISTENT.', @GROUP

               print ' '
            end

         update #grprght2
         set groupname = ' ',
             db        = ' '
         from #grprght2 g
         where exists (select *
                       from #grprght2 g2
                       where g2.groupname = g.groupname
                         and g2.db        = g.db
                         and g2.username  = g.username
                         and g2.username  > (select min (g3.username)
                                             from #grprght2 g3
                                             where g3.groupname = g2.groupname
                                               and g3.db        = g2.db))

         update #grprght2
         set groupname = ' '
         from #grprght2 g
         where exists (select *
                       from #grprght2 g2
                       where g2.groupname = g.groupname
                         and g2.db        = g.db
                         and g2.db        > (select min (g3.db)
                                             from #grprght2 g3
                                             where g3.groupname = g2.groupname))

         update #grprght2
         set username = substring (username + ' (' + sysname + ')', 1, 30)
         where username <> sysname

         if (@GROUP IS NULL)
            begin
               insert into #grprght2
                  select ' ',
                         ' ',
                         ' ',
                         ' ',
                         min (substring (sort, 1, charindex (' ', sort)))
                  from #grprght2
                  where groupname <> ' '
                  group by groupname
                  having groupname <> ' '

               delete from #grprght2
               where groupname = ' '
                 and sort      = (select min (sort)
                                  from #grprght2)
            end

         select 'GROUP'    = substring (groupname, 1, 22),
                'DATABASE' = substring (db, 1, 23),
                USERNAME   = username
         from #grprght2
         order by sort
      end
   else
      begin
         if (@GROUP IS NULL)
            begin
               print '    ALL GROUPS ARE CONSISTENT ACROSS ALL DATABASES.'
            end
         else if (not exists (select *
                              from #groupsright))
            begin
               print '    GROUP <%1!> DOES NOT EXIST IN ANY DATABASE.', @GROUP
            end
      end

   print ' '
go
sp_procxmode sp_groupsright_print, anymode
go

drop table #groupsright
go
drop table #grprght2
go


create procedure sp_groupsright (@GROUP varchar (30) = NULL)  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

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

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

         print ' '

         return 1
      end

   print ' '

   declare @cmdstring  varchar (30)

   create table #groupsright
      (db        varchar (30)  not null,
       suid      smallint      not null,
       username  varchar (30)  not null,
       groupname varchar (30)  not null)

   create table #grprght2
      (groupname varchar (30)  not null,
       db        varchar (30)  not null,
       username  varchar (30)  not null,
       sysname   varchar (30)  null,
       sort      varchar (92)  not null)

   print '    LOADING WORK TABLE FROM ALL DATABASES.'

   print ' '

   select @cmdstring = 'sp_groupsright_load'

   execute sp_globalexec  @cmdstring, @GROUP

   create unique clustered index UC on #groupsright (groupname, db, suid)

   delete #groupsright
   from #groupsright t1
   where not exists (select t2.*
                     from #groupsright t2
                     where t2.groupname  = t1.groupname
                       and t2.db        <> t1.db)

   insert into #grprght2
      select t1.groupname,
             t1.db,
             t1.username,
             l.name,
             t1.groupname  +  ' '  +  t1.db  +  ' '  +  t1.username
      from #groupsright t1,
           master.dbo.syslogins l
      where t1.suid *= l.suid
        and t1.groupname in (select distinct t2.groupname
                             from #groupsright t2
                             where exists (select t3.*
                                           from #groupsright t3
                                           where t3.groupname  = t2.groupname
                                             and t3.db        <> t2.db
                                             and not exists (select t4.*
                                                             from #groupsright t4
                                                             where t4.groupname = t3.groupname
                                                               and t4.db        = t3.db
                                                               and t4.suid      = t2.suid)))

   create unique clustered index UC on #grprght2 (groupname, db, username, sort)

   execute sp_groupsright_print @GROUP
go

sp_addcomment sp_groupsright, 12, 'Checks server consistencies for GROUP or (default) all groups'
go
sp_procxmode sp_groupsright, anymode
go

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

         print ' '

         return 1
      end

   if (db_id () = 1)
      begin
         if (exists (select l.suid
                     from master.dbo.syslogins l,
                          master.dbo.syssrvroles r
                     where l.name = r.name))
            begin
               select 'IDENTICAL LOGINS/ROLES' = l.name
               from master.dbo.syslogins l,
                    master.dbo.syssrvroles r
               where l.name = r.name
               order by 'IDENTICAL LOGINS/ROLES'
            end
         else
            begin
               print '    ALL LOGINS AND ROLES HAVE DIFFERENT NAMES.'
            end
      end

   print ' '

   if (1 < any (select count (uid)
                from sysobjects
                group by name))
      begin
         select OWNER = substring (isnull (user_name (o.uid), '?'), 1, 16),
                NAME  = o.name,
                TYPE  = substring (vO.name, 1, 23)
         from sysobjects o,
              sybsystemprocs.dbo.usptvalues vO
         where 1 < (select count (o2.uid)
                    from sysobjects o2
                    where o2.name = o.name)
           and vO.number = convert (int, o.sysstat & 15)
           and vO.type   = 'O'
         group by o.name
         having 1 < (select count (o2.uid)
                     from sysobjects o2
                     where o2.name = o.name)
            and vO.number = convert (int, o.sysstat & 15)
            and vO.type   = 'O'
         order by o.name, user_name (o.uid), vO.name
      end
   else
      begin
         print '    ALL SYSOBJECT NAMES ARE UNIQUE.'

         print ' '
      end
go
sp_addcomment sp_objectsunique, 11, 'Checks for existence of duplicate names'
go
sp_procxmode sp_objectsunique, anymode
go

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

         print ' '

         return 1
      end

   declare @maxkeycnt  tinyint,
           @seq        tinyint

   create table #refkeysright
     (constrid   int           not null,
      tableid    int           not null,            -- Also used for final sort
      reftabid   int           not null,
      keycnt     tinyint       not null,
      pmrydbid   smallint      not null,
      foseq      tinyint       not null,            -- Also used for text sequence
      fokey      tinyint       not null,
      nullflag   tinyint       not null,
      fktext     varchar (255) null,
      pktext     varchar (30)  null)

   if (exists (select *
               from sysreferences))
      begin
         if (exists (select *
                     from sysreferences
                     where (frgndbid <> db_id ()  and  pmrydbid <> db_id ())
                        or col_name (tableid, fokey1, frgndbid)   IS NULL
                        or col_name (reftabid, refkey1, pmrydbid) IS NULL))
            begin
               print '    THERE ARE INVALID REFERENTIAL CONSTRAINTS.'

               print ' '

                  select 'REFERENCE NAME'     = space (15),
                         'FOREIGN KEY TABLE'  = space (30),
                         'PRIMARY KEY TABLE'  = space (30)
                  where 1 = 2
               union
                  select substring (isnull (object_name (constrid), '?'),
                                    1,
                                    15),
                         isnull (db_name (frgndbid), 'unknown database'),
                         isnull (db_name (pmrydbid), 'unknown database')
                  from sysreferences
                  where frgndbid <> db_id ()
                    and pmrydbid <> db_id ()
               union
                  select substring (isnull (object_name (r.constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (r.frgndbid), '?')
                                    + '.'
                                    + isnull (user_name (o.uid), '?')
                                    + '.'
                                    + isnull (o.name, '?'),
                                    1,
                                    30),
                         substring (isnull (db_name (r.pmrydbid), '?')
                                    + '/'
                                    + isnull (object_name (r.reftabid, r.pmrydbid), '?'),
                                    1,
                                    30)
                  from sysobjects    o,
                       sysreferences r
                  where r.frgndbid  = db_id ()
                    and r.pmrydbid <> db_id ()
                    and ((col_name (r.tableid, r.fokey1, r.frgndbid)   IS NULL)  or
                         (col_name (r.reftabid, r.refkey1, r.pmrydbid) IS NULL))
                    and r.tableid  *= o.id
               union
                  select substring (isnull (object_name (r.constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (r.frgndbid), '?')
                                    + '/'
                                    + isnull (object_name (r.tableid, r.frgndbid), '?'),
                                    1,
                                    30),
                         substring (isnull (db_name (r.pmrydbid), '?')
                                    + '.'
                                    + isnull (user_name (o.uid), '?')
                                    + '.'
                                    + isnull (o.name, '?'),
                                    1,
                                    30)
                  from sysobjects    o,
                       sysreferences r
                  where r.frgndbid <> db_id ()
                    and r.pmrydbid  = db_id ()
                    and ((col_name (r.tableid, r.fokey1, r.frgndbid)   IS NULL)  or
                         (col_name (r.reftabid, r.refkey1, r.pmrydbid) IS NULL))
                    and r.reftabid *= o.id
               union
                  select substring (isnull (object_name (r.constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (r.frgndbid), '?')
                                    + '.'
                                    + isnull (user_name (fo.uid), '?')
                                    + '.'
                                    + isnull (object_name (r.tableid, r.frgndbid), '?'),
                                    1,
                                    30),
                         substring (isnull (db_name (r.pmrydbid), '?')
                                    + '.'
                                    + isnull (user_name (ro.uid), '?')
                                    + '.'
                                    + isnull (object_name (r.reftabid, r.pmrydbid), '?'),
                                    1,
                                    30)
                  from sysobjects    fo,
                       sysobjects    ro,
                       sysreferences r
                  where r.frgndbid  = db_id ()
                    and r.pmrydbid  = db_id ()
                    and ((col_name (r.tableid, r.fokey1, r.frgndbid)   IS NULL)  or
                         (col_name (r.reftabid, r.refkey1, r.pmrydbid) IS NULL))
                    and r.tableid  *= fo.id
                    and r.reftabid *= ro.id
               order by 'REFERENCE NAME', 'FOREIGN KEY TABLE'
            end
         else
            begin
               print '    THERE ARE NO INVALID REFERENTIAL CONSTRAINTS.'
            end

         print ' '

         if (exists (select *
                     from sysreferences
                     where db_id (pmrydbname) <> pmrydbid
                        or db_id (frgndbname) <> frgndbid))
            begin
               print '    THERE ARE INCONSISTENT REFERENTIAL DATABASE NAME/ID''S.'

               print ' '

                  select 'REFERENCE NAME' = space (15),
                         'REFERENCING DB' = space (30),
                         'REFERENCED DB'  = space (30)
                  where 1 = 2
               union
                  select substring (isnull (object_name (constrid), '?'),
                                    1,
                                    15),
                         substring (isnull (db_name (frgndbid), '?')
                                    + ' / '
                                    + frgndbname,
                                    1,
                                    30),
                         substring (isnull (db_name (pmrydbid), '?')
                                    + ' / '
                                    + pmrydbname,
                                    1,
                                    30)
                  from sysreferences
                  where db_id (frgndbname) <> frgndbid
                     or db_id (pmrydbname) <> pmrydbid
               order by 'REFERENCE NAME', 'REFERENCING DB'
            end
         else
            begin
               print '    REFERENTIAL DATABASE NAMES/IDS ARE CONSISTENT.'
            end

         print ' '

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 1, fokey1, 0, NULL, NULL
            from sysreferences

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 2, fokey2, 0, NULL, NULL
            from sysreferences
            where fokey2 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 3, fokey3, 0, NULL, NULL
            from sysreferences
            where fokey3 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 4, fokey4, 0, NULL, NULL
            from sysreferences
            where fokey4 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 5, fokey5, 0, NULL, NULL
            from sysreferences
            where fokey5 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 6, fokey6, 0, NULL, NULL
            from sysreferences
            where fokey6 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 7, fokey7, 0, NULL, NULL
            from sysreferences
            where fokey7 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 8, fokey8, 0, NULL, NULL
            from sysreferences
            where fokey8 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 9, fokey9, 0, NULL, NULL
            from sysreferences
            where fokey9 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 10, fokey10, 0, NULL, NULL
            from sysreferences
            where fokey10 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 11, fokey11, 0, NULL, NULL
            from sysreferences
            where fokey11 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 12, fokey12, 0, NULL, NULL
            from sysreferences
            where fokey12 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 13, fokey13, 0, NULL, NULL
            from sysreferences
            where fokey13 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 14, fokey14, 0, NULL, NULL
            from sysreferences
            where fokey14 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 15, fokey15, 0, NULL, NULL
            from sysreferences
            where fokey15 <> 0

         insert into #refkeysright
            select constrid, tableid, reftabid, keycnt, pmrydbid, 16, fokey16, 0, NULL, NULL
            from sysreferences
            where fokey16 <> 0

         update #refkeysright
         set nullflag = 1
         from syscolumns c
         where tableid        = c.id
           and fokey          = c.colid
           and (c.status & 8) = 8

         delete from #refkeysright
         where keycnt = 1

         delete from #refkeysright
         where constrid not in (select distinct c2.constrid
                                from #refkeysright c2
                                where c2.nullflag = 1)

         if (exists (select *
                     from #refkeysright))
            begin
               print '    THERE ARE [NULLABLE FOREIGN SUBKEYS].'

               print ' '

               select @maxkeycnt = max (keycnt),
                      @seq       = 2
               from #refkeysright

               update #refkeysright
               set fktext = object_name (tableid)
               where foseq = 1

               update #refkeysright
               set fktext = substring (user_name (o.uid) + '.' + fktext, 1, 30)
               from sysobjects o
               where foseq   = 1
                 and tableid = o.id
                 and (o.uid <> 1  or
                      1      < (select count (o2.uid)
                                from sysobjects o2
                                where o2.name           = o.name
                                  and (o2.sysstat & 15) = 3))

               update #refkeysright
               set fktext = fktext
                            + ' ('
                            +  ltrim (char (32 + (59 * (nullflag))))
                            +  col_name (tableid, fokey)
                            +  ltrim (char (32 + (61 * (nullflag))))
                            +  ', '
               where foseq = 1

               -- Note that if the pseudonymn order is reversed in the following
               -- from clause then the results will change.

               while (@seq <= @maxkeycnt)
                  begin
                     update #refkeysright
                     set r1.fktext = r1.fktext
                                     + ' '
                                     +  ltrim (char (32 + (59 * (r2.nullflag))))
                                     +  col_name (r2.tableid, r2.fokey)
                                     +  ltrim (char (32 + (61 * (r2.nullflag))))
                                     +  ', '
                     from #refkeysright r1,
                          #refkeysright r2
                     where r1.constrid = r2.constrid
                       and r1.foseq    = 1
                       and r2.foseq    = @seq

                     select @seq = @seq + 1
                  end

               delete from #refkeysright
               where foseq <> 1

               update #refkeysright
               set fktext = substring (fktext, 1, char_length (fktext) - 1)   +  ')'
               where char_length (fktext) < 255

               update #refkeysright
               set pktext = object_name (reftabid)

               update #refkeysright
               set pktext = substring (user_name (o.uid) + '.' + pktext, 1, 30)
               from sysobjects o
               where reftabid = o.id
                 and (o.uid <> 1  or
                      1      < (select count (o2.uid)
                                from sysobjects o2
                                where o2.name           = o.name
                                  and (o2.sysstat & 15) = 3))

               update #refkeysright
               set tableid = constrid

               insert into #refkeysright
                  select 0, tableid, reftabid, keycnt, pmrydbid, 2, 0, 0, '   ' + ltrim (substring (fktext, 45, 255)), ' '
                  from #refkeysright
                  where char_length (fktext) > 44

               insert into #refkeysright
                  select 0, tableid, reftabid, keycnt, pmrydbid, 3, 0, 0, '   ' + ltrim (substring (fktext, 45, 255)), ' '
                  from #refkeysright
                  where foseq                = 2
                    and char_length (fktext) > 44

               select 'REFERENCE NAME'    = isnull (substring (object_name (constrid), 1, 14), ' '),
                      'FOREIGN KEY'       = substring (fktext, 1, 44),
                      'PRIMARY KEY TABLE' = substring (pktext, 1, 17)
               from #refkeysright
               order by object_name (tableid), foseq
            end
         else
            begin
               print '    THERE ARE NO NULLABLE FOREIGN SUBKEYS.'
            end

         print ' '

         truncate table #refkeysright

         insert into #refkeysright
            select r.constrid, r.tableid, r.reftabid, 0, r.pmrydbid, 0, 0, 0,
                   object_name (r.tableid), NULL
            from sysreferences r
            where r.frgndbname IS NULL
              and exists (select r2.constrid
                          from sysreferences r2
                          where r2.frgndbname IS NULL
                            and r2.tableid     = r.tableid
                            and r2.constrid   <> r.constrid
                            and ((r2.fokey1 = r.fokey1)  or
                                 (r2.fokey1 = r.fokey2)  or
                                 (r2.fokey2 = r.fokey1)))

         if (exists (select *
                     from #refkeysright))
            begin
               print '    THERE ARE OVERLAPPING AND/OR DUPLICATE FOREIGN KEYS.'

               print ' '

               update #refkeysright
               set fktext = substring (user_name (o.uid) + '.' + fktext, 1, 30)
               from sysobjects o
               where tableid = o.id
                 and (o.uid <> 1  or
                      1      < (select count (o2.uid)
                                from sysobjects o2
                                where o2.name           = o.name
                                  and (o2.sysstat & 15) = 3))

               update #refkeysright
               set r1.fktext = substring (r1.fktext
                                          +  ' ('
                                          +  col_name (r2.tableid, r2.fokey1)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey2)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey3)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey4)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey5)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey6)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey7)
                                          +  ', '
                                          +  col_name (r2.tableid, r2.fokey8),
                                          1,
                                          255)
               from #refkeysright r1,
                    sysreferences r2
               where r1.constrid = r2.constrid

               update #refkeysright
               set fktext = substring (fktext, 1, charindex (', ,', fktext) - 1)
               where charindex (', ', fktext) > 0

               update #refkeysright
               set fktext = substring (fktext + ')', 1, 255)

               select 'REFERENCE NAME'    = isnull (substring (object_name (constrid), 1, 14), '?'),
                      'FOREIGN KEY'       = substring (fktext, 1, 44),
                      'PRIMARY KEY TABLE' = substring (object_name (reftabid, pmrydbid), 1, 17)
               from #refkeysright
               order by fktext, object_name (reftabid, pmrydbid)
            end
         else
            begin
               print '    THERE ARE NO OVERLAPPING OR DUPLICATE FOREIGN KEYS.'
            end
      end
   else
      begin
         print '    THERE ARE NO REFERENTIAL CONSTRAINTS.'
      end

   print ' '
go
sp_addcomment sp_refkeysright, 11, 'Checks for invalid referential keys'
go
sp_procxmode sp_refkeysright, anymode
go

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

         print ' '

         return 1
      end

   declare @attribid     smallint,
           @attribclass  smallint,
           @level        tinyint

   create table #rr_tree
     (tlevel       tinyint       not null,
      parentid     int           not null,
      parentname   varchar (30)  not null,
      middleid     int           not null,
      middlename   varchar (30)  not null,
      roleid       int           null,
      rolename     varchar (30)  not null)

   create table #rolesright
     (lgn          varchar (30)  not null,
      highrole     varchar (37)  not null,
      hr_status    tinyint       not null,
      hr_pwd       varchar (3)   null,
      subrole      varchar (41)  not null,
      sr_status    tinyint       not null,
      sr_pwd       varchar (3)   null)

   -- FLATTEN THE ROLE TREES; STORE TOP, "MIDDLE", AND LEAF ROLES.

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

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

   while (exists (select *
                  from #rr_tree
                  where tlevel      = @level - 1
                    and roleid IS NOT NULL))
      begin
         insert into #rr_tree
            select @level,
                   dc.parentid,
                   dc.parentname,
                   dc.roleid,
                   dc.rolename,
                   a.object_info1,
                   role_name (a.object_info1)
            from master.dbo.sysattributes a,
                 #rr_tree dc
            where dc.tlevel   = @level - 1
              and a.object    = dc.roleid
              and a.class     = @attribclass
              and a.attribute = @attribid

         select @level = @level + 1
      end

   -- LOAD POTENTIAL PROBLEMS INTO #rolesright

   -- LOGIN HAS BOTH ROLE & SUB-ROLE PRIVILEGES

   insert into #rolesright
      select l.name,
             rr.parentname,
             lr1.status,
             substring (sr1.password, 1, 3),
             rr.rolename,
             lr2.status,
             substring (sr2.password, 1, 3)
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr1,
           master.dbo.syssrvroles sr1,
           #rr_tree rr,
           master.dbo.sysloginroles lr2,
           master.dbo.syssrvroles sr2
      where lr1.suid          = l.suid
        and sr1.srid          = lr1.srid
        and rr.parentname     = sr1.name
        and lr2.suid          = l.suid
        and sr2.srid          = lr2.srid
        and rr.rolename       = sr2.name

   -- SUB-ROLE IS PASSWORD PROTECTED

   insert into #rolesright
      select distinct '-', rr.parentname, 0, NULL, rr.rolename, 0, 'A'
      from #rr_tree rr,
           master.dbo.syssrvroles sr
      where sr.name      = rr.rolename
        and sr.password IS NOT NULL

   insert into #rolesright
      select distinct '-', rr.middlename, 0, NULL, rr.rolename, 0, 'B'
      from #rr_tree rr,
           master.dbo.syssrvroles sr
      where sr.name      = rr.rolename
        and sr.password IS NOT NULL

   -- LOGIN HAS PASSWORD PROTECTED DEFAULT ROLE

   insert into #rolesright
      select l.name, sr.name, 1, 'X', '-', 0, NULL
      from master.dbo.syslogins l,
           master.dbo.sysloginroles lr,
           master.dbo.syssrvroles sr
      where lr.suid      = l.suid
        and lr.status    > 0
        and sr.srid      = lr.srid
        and sr.password IS NOT NULL

   if (exists (select *
               from #rolesright))
      begin
         -- LET'S CORRECT THE DEFAULT STATUS FOR SYSTEM SUB-ROLES

         update #rolesright
         set sr_status = 1
         from #rolesright rr,
              master.dbo.syssrvroles sr
         where rr.sr_status <> 1
           and sr.name       = rr.subrole
           and sr.srid       < 32

         -- LET'S MASSAGE THE TEXT AND PRINT IT.

         update #rolesright
         set highrole = highrole + ' (dflt, pwd)'
         where hr_status = 1
           and hr_pwd    IS NOT NULL

         update #rolesright
         set highrole = highrole + ' (dflt)'
         where hr_status = 1
           and hr_pwd    IS NULL

         update #rolesright
         set highrole = highrole + ' (pwd)'
         where hr_status = 0
           and hr_pwd    IS NOT NULL

         update #rolesright
         set subrole = subrole + ' (pwd, dflt)'
         where sr_status  = 1
           and sr_pwd    IS NOT NULL

         update #rolesright
         set subrole = subrole + ' (dflt)'
         where sr_status  = 1
           and sr_pwd    IS NULL

         update #rolesright
         set subrole = subrole + ' (pwd)'
         where sr_status  = 0
           and sr_pwd    IS NOT NULL

         select distinct LOGIN            = substring (lgn, 1, 23),
                         'LOGIN ROLE'     = substring (highrole, 1, 26),
                         'LOGIN SUB-ROLE' = substring (subrole, 1, 26)
         from #rolesright
         order by LOGIN, 'LOGIN ROLE', 'LOGIN SUB-ROLE'
      end
   else
      begin
         print '    NO POTENTIAL ROLE PROBLEMS WERE DETECTED.'

         print ' '
      end
go
sp_addcomment sp_rolesright, 11, 'Checks for potential role configuration management problems'
go
sp_procxmode sp_rolesright, anymode
go

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

         print ' '

         return 1
      end

   if ((exists (select o.*
                from sysdepends d,
                     sysobjects o
                where o.id                   = d.id
                  and object_name (d.depid) IS NULL))   or
       (exists (select o.*
                from sysdepends d,
                     sysdepends d2,
                     sysobjects o
                where o.id                    = d.id
                  and d.depid                 = d2.id
                  and object_name (d2.depid) IS NULL))  or
       (exists (select o.*
                from sysdepends d,
                     sysobjects o,
                     sysobjects o2
                where o.id     = d.id
                  and d.depid  = o2.id
                  and o.crdate < o2.crdate))            or
       (exists (select o.*
                from sysdepends d,
                     sysdepends d2,
                     sysobjects o,
                     sysobjects o2
                where o.id     = d.id
                  and d.depid  = d2.id
                  and o2.id    = d2.id
                  and o.crdate < o2.crdate)))
      begin
         create table #usesright
            (objid     int               not null,
             objuid    smallint          not null,
             objname   varchar (30)      not null,
             objdate   datetime          null,
             basename  varchar (30)      not null,
             basedate  datetime          null,
             sort      varchar (61)      null)

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, convert (char (20), d.depid), NULL, NULL
            from sysdepends d,
                 sysobjects o
            where o.id      = d.id
              and object_name (d.depid) IS NULL

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, convert (varchar (20), d2.depid) + ' ##', NULL, NULL
            from sysdepends d,
                 sysdepends d2,
                 sysobjects o
            where o.id      = d.id
              and d.depid   = d2.id
              and object_name (d2.depid) IS NULL

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, o2.name, o2.crdate, NULL
            from sysdepends d,
                 sysobjects o,
                 sysobjects o2
            where o.id      = d.id
              and o2.id     = d.depid
              and o.crdate  < o2.crdate

         insert into #usesright
            select o.id, o.uid, o.name, o.crdate, o2.name, o2.crdate, NULL
            from sysdepends d,
                 sysdepends d2,
                 sysobjects o,
                 sysobjects o2
            where o.id      = d.id
              and d.depid   = d2.id
              and o2.id     = d.id
              and o.crdate  < o2.crdate

         update #usesright
         set objname = substring (u.objname + '(' + user_name (u.objuid) + ')', 1, 30)
         from #usesright u
         where u.objuid <> 1
            or 1         < (select count (distinct o.uid)
                            from sysobjects o
                            where o.name = u.objname)

         update #usesright
         set sort = substring (objname + ' ' + basename, 1, 61)

         update #usesright
         set objname = ' ',
             objdate = NULL
         from #usesright ur
         where exists (select *
                       from #usesright ur2
                       where ur2.objname  = ur.objname
                         and ur2.basename = ur.basename
                         and ur2.basename > (select min (ur3.basename)
                                             from #usesright ur3
                                             where ur3.objname = ur2.objname))

         select 'OBJECT'       = substring (objname, 1, 25),
                'OBJECT DATE'  = isnull (convert (char (8), objdate, 11), ' ') + ' ' + convert (char (5), objdate, 8),
                'BASE OBJECT?' = substring (basename, 1, 21),
                'BASE DATE'    = isnull (convert (char (8), basedate, 11), '?') + ' ' + convert (char (5), basedate, 8)
         from #usesright
         order by sort
      end
   else
      begin
         print '    ALL OBJECTS ARE CONSISTENT WITH THEIR BASE OBJECTS.'
      end

   print ' '
go
sp_addcomment sp_usesright, 11, 'Checks for objects with invalid/newer base objects'
go
sp_procxmode sp_usesright, anymode
go

dump transaction sybsystemprocs with truncate_only
go
