use sybsystemprocs
go

sp_dropprocedure sp_columnsunique
go
sp_dropprocedure sp_domainsright_col_cnstrnts
go
sp_dropprocedure sp_domainsright_col_ut_rules
go
sp_dropprocedure sp_domainsright
go
sp_dropprocedure sp_groupsright_load
go
sp_dropprocedure sp_groupsright_print
go
sp_dropprocedure sp_groupsright
go
sp_dropprocedure sp_objectsunique
go
sp_dropprocedure sp_rolesright
go
sp_dropprocedure 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, 'Checks consistency of user column names by type/length'
go
sp_addcomment sp_columnsunique, '   if NULL_FLAG is non-null also check by nullability'
go
sp_procxmode sp_columnsunique, 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),
           @TClen      tinyint,
           @cmdstring  varchar (255),
           @cnstrlen   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 '%),'

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

   select @TClen    = max (char_length (TC)),
          @cnstrlen = max (char_length (cnstrtxt))
   from #dr23

   while ((@TClen + @cnstrlen) > 64)
      begin
         if (@TClen > 21)
            begin
               select @TClen = @TClen - 1
            end

         if (@cnstrlen > 19)
            begin
               select @cnstrlen = @cnstrlen - 1
            end
      end

   select @cmdstring = 'select ''TABLE.COLUMN'' = substring (TC, 1,'
                       +  convert (varchar (2), @TClen)
                       +  '),'
                       +  ' TYPE = cnstrtype,'
                       +  ' DETAIL = substring (cnstrtxt, 1,'
                       +  convert (varchar (2), @cnstrlen)
                       +  ')'
                       +  ' from #dr23'
                       +  ' order by sorttext, colid, cnstrtype, cnstrtxt'

   execute  (@cmdstring)
go
sp_existsprocedure sp_domainsright_col_cnstrnts
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),
           @cmdstring  varchar (255),
           @chklen     tinyint,
           @tclen      tinyint,
           @utdfltlen  tinyint,
           @utlen      tinyint

   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 @tclen     = max (char_length (TC)),
          @chklen    = max (char_length (cnstrtxt)),
          @utlen     = max (char_length (uttxt)),
          @utdfltlen = max (char_length (utdflttxt))
   from #dr45

   select @cmdstring = 'select ''TABLE.COLUMN'' = substring (TC, 1,'
                       +  convert (varchar (2), @tclen)
                       +  '),'
                       +  '''RULE / CHECK'' = substring (cnstrtxt, 1,'
                       +  convert (varchar (2), @chklen)
                       +  '),'
                       +  'USERTYPE = substring (uttxt, 1,'
                       +  convert (varchar (2), @utlen)
                       +  '),'
                       +  '''USERTYPE RULE'' = substring (utdflttxt, 1,'
                       +  convert (varchar (2), @utdfltlen)
                       +  ')'
                       +  ' from #dr45'
                       +  ' order by sorttxt, cnstrtxt'

   execute  (@cmdstring)
go
sp_existsprocedure sp_domainsright_col_ut_rules
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),
           @cmdstring  varchar (255),
           @chklen     tinyint,
           @collen     tinyint,
           @dfltlen    tinyint,
           @ilen       tinyint,
           @tablen     tinyint,
           @tclen      tinyint,
           @utdfltlen  tinyint,
           @utlen      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)  and
       (not exists (select *
                    from master.dbo.sysdatabases
                    where dbid            = db_id ()
                      and (status & 1024) = 1024)))
      begin
         print '    UPDATING INDEX STATISTICS ON SYSTEM TABLES.'

         print ' '

         update index statistics syscomments

         update index 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 @ilen = max (char_length (itext))
         from #dr1

         select @cmdstring = 'select IDENTIFIER = substring (itext, 1,'
                             +  convert (varchar (2), @ilen)
                             +  '),'
                             +  '''IDENTIFIER TYPE'' = itype'
                             +  ' from #dr1'
                             +  ' order by itext, itype'

         execute  (@cmdstring)
      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 @chklen = max (char_length (cnstrtxt)),
                @tclen  = max (char_length (TC))
         from #dr23

         select @cmdstring = 'select ''TABLE.COLUMN'' = substring (TC, 1,'
                             +  convert (varchar (2), @tclen)
                             +  '),'
                             +  '''CHECK'' = substring (cnstrtxt, 1,'
                             +  convert (varchar (2), @chklen)
                             +  ')'
                             +  ' from #dr23'
                             +  ' order by sorttext, cnstrtxt'

         execute  (@cmdstring)
      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 @tclen     = max (char_length (TC)),
                @dfltlen   = max (char_length (cnstrtxt)),
                @utlen     = max (char_length (uttxt)),
                @utdfltlen = max (char_length (utdflttxt))
         from #dr45

         select @cmdstring = 'select ''TABLE.COLUMN'' = substring (TC, 1,'
                             +  convert (varchar (2), @tclen)
                             +  '),'
                             +   '''DEFAULT'' = substring (cnstrtxt, 1,'
                             +  convert (varchar (2), @dfltlen)
                             +  '),'
                             +  'USERTYPE = substring (uttxt, 1,'
                             +  convert (varchar (2), @utlen)
                             +  '),'
                             +  '''USERTYPE DEFAULT'' = substring (utdflttxt, 1,'
                             +  convert (varchar (2), @utdfltlen)
                             +  ')'
                             +  ' from #dr45'
                             +  ' order by TC'

         execute  (@cmdstring)
      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 @tablen    = max (char_length (tabletext)),
                @collen    = max (char_length (coltext)),
                @chklen    = max (char_length (object_name (domain)))
         from #dr6

         select @cmdstring = 'select ''TABLE'' = substring (tabletext, 1,'
                             +  convert (varchar (2), @tablen)
                             +  '),'
                             +  '''COLUMN'' = substring (coltext, 1,'
                             +  convert (varchar (2), @collen)
                             +  '),'
                             +  '''CHECK/RULE'' = substring (object_name (domain), 1,'
                             +  convert (varchar (2), @chklen)
                             +  ')'
                             +  ' from #dr6'
                             +  ' order by object_name (id), user_name (uid), colid'

         execute  (@cmdstring)
      end
   else
      begin
         print '    NO BIT COLUMNS HAVE A CHECK OR RULE.'
      end

   print ' '
go
sp_addcomment sp_domainsright, '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 #groupserr
   (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_existsprocedure sp_groupsright_load
go

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

   declare @cmdstring  varchar (255),
           @dblen      tinyint,
           @groupcnt   varchar (  7),
           @grouplen   tinyint,
           @userlen    tinyint

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

         print ' '
      end

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

         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 #groupserr
         set groupname = ' ',
             db        = ' '
         from #groupserr g
         where exists (select *
                       from #groupserr g2
                       where g2.groupname = g.groupname
                         and g2.db        = g.db
                         and g2.username  = g.username
                         and g2.username  > (select min (g3.username)
                                             from #groupserr g3
                                             where g3.groupname = g2.groupname
                                               and g3.db        = g2.db))

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

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

         select @grouplen = max (char_length (groupname)),
                @dblen    = max (char_length (db)),
                @userlen  = max (char_length (username))
         from #groupserr

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

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

         select @cmdstring = 'select ''GROUP'' = substring (groupname, 1,'
                             +  convert (varchar (2), @grouplen)
                             +  '),'
                             +  '''DATABASE'' = substring (db, 1,'
                             +  convert (varchar (2), @dblen)
                             +  '),'
                             +  'USERNAME = substring (username, 1,'
                             +  convert (varchar (2), @userlen)
                             +  ')'
                             +  ' from #groupserr'
                             +  ' order by sort'

         execute  (@cmdstring)
      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_existsprocedure sp_groupsright_print
go
sp_procxmode sp_groupsright_print, anymode
go

drop table #groupsright
go
drop table #groupserr
go


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

   print ' '

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

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

         print ' '

         return 1
      end

   if (charindex ('sa_role', show_role ()) = 0)
      begin
         print '    ONLY AN ADMINISTRATOR CAN EXECUTE sp_groupsright.'

         print ' '

         return 1
      end

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

   create table #groupserr
      (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 ON SERVER %1!.', @@servername

   execute  sp_globalexec  @UTILITY_SP='sp_groupsright_load', @ARG1=@GROUP

   print '    ANALYZING WORK TABLE.'

   print ' '

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

   -- ELIMINATE GROUPS THAT ARE IN ONLY ONE DATABASE

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

    -- g1: ACQUIRE LOGIN NAME FOR PROBLEM GROUP USERS
    -- g2: SAME GROUP AS g1 BUT DIFFERENT DATABASE
    -- g3: SAME GROUP AND DATABASE AS g2 BUT NO suid IN g1

   insert into #groupserr
      select distinct g1.groupname,
                      g1.db,
                      g1.username,
                      l.name,
                      g1.groupname  +  ' '  +  g1.db  +  ' '  +  g1.username
      from #groupsright g1  left join  master.dbo.syslogins l
        on g1.suid = l.suid
      where exists (select g2.*
                    from #groupsright g2
                    where g2.groupname  = g1.groupname
                      and g2.db        <> g1.db
                      and not exists (select g3.*
                                      from #groupsright g3
                                      where g3.groupname = g2.groupname
                                        and g3.db        = g2.db
                                        and g3.suid      = g1.suid))

   -- LOAD MINIMAL DATABASES/GROUPS TO CLARIFY MISSING GROUP USERS

   insert into #groupserr
      select distinct g1.groupname,
                      g1.db,
                      '-',
                      '-',
                      g1.groupname  +  ' '  +  g1.db  +  ' -'
      from #groupsright g1
      where     exists (select g2.*
                        from #groupserr g2
                        where g2.groupname = g1.groupname)
        and not exists (select g3.*
                        from #groupserr g3
                        where g3.groupname = g1.groupname
                          and g3.db        = g1.db)

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

   execute  sp_groupsright_print  @GROUP=@GROUP
go

sp_addcomment sp_groupsright, '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

   declare @cmdstring  varchar (255),
           @objlen     tinyint,
           @ownerlen   tinyint,
           @typelen    tinyint

   create table #objunique
      (owner    varchar (30)  not null,
       objname  varchar (30)  not null,
       type     varchar (30)  not null)

   insert into #objunique
      select isnull (user_name (o.uid), '?'), o.name, 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'

   if (exists (select *
               from #objunique))
      begin
         select @objlen   = max (char_length (objname)),
                @ownerlen = max (char_length (owner)),
                @typelen  = max (char_length (type))
         from #objunique

         select @cmdstring = 'select OWNER = substring (owner, 1,'
                             +  convert (varchar (2), @ownerlen)
                             +  '),'
                             +  'NAME = substring (objname, 1,'
                             +  convert (varchar (2), @objlen)
                             +  '),'
                             +  'TYPE = substring (type, 1,'
                             +  convert (varchar (2), @typelen)
                             +  ')'
                             +  ' from #objunique'
                             +  ' order by NAME, OWNER, TYPE'

         execute  (@cmdstring)
      end
   else
      begin
         print '    ALL SYSOBJECT NAMES ARE UNIQUE.'

         print ' '
      end
go
sp_addcomment sp_objectsunique, 'Checks for existence of duplicate names'
go
sp_procxmode sp_objectsunique, 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,
           @cmdstring    varchar (255),
           @hrlen        tinyint,
           @level        tinyint,
           @lgnlen       tinyint,
           @sublen       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 ((charindex ('sso_role', show_role ()) = 0)  and
       (charindex ('sa_role', show_role ())  = 0))
      begin
         delete from #rolesright
         where lgn <> suser_name ()
      end

   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 @lgnlen   = max (char_length (lgn)),
                @hrlen    = max (char_length (highrole)),
                @sublen   = max (char_length (subrole))
         from #rolesright

         select @cmdstring = 'select distinct LOGIN = substring (lgn, 1,'
                             +  convert (varchar (2), @lgnlen)
                             +  '),'
                             +  '''LOGIN ROLE'' = substring (highrole, 1,'
                             +  convert (varchar (2), @hrlen)
                             +  '),'
                             +  '''LOGIN SUB-ROLE'' = substring (subrole, 1,'
                             +  convert (varchar (2), @sublen)
                             +  ')'
                             +  ' from #rolesright'
                             +  ' order by LOGIN, ''LOGIN ROLE'', ''LOGIN SUB-ROLE'''

         execute  (@cmdstring)
      end
   else
      begin
         print '    NO POTENTIAL ROLE PROBLEMS WERE DETECTED.'

         print ' '
      end
go
sp_addcomment sp_rolesright, '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, 'Checks for objects with invalid/newer base objects'
go
sp_procxmode sp_usesright, anymode
go

dump transaction sybsystemprocs with truncate_only
go
