use sybsystemprocs
go

sp_dropprocedure sp_grant
go
sp_dropprocedure sp_grantsobjdetail
go
sp_dropprocedure sp_grantsuserdetail
go
sp_dropprocedure sp_grants
go
sp_dropprocedure sp_grantsall
go
sp_dropprocedure sp_grantscreate
go
sp_dropprocedure sp_grantsme
go
sp_dropprocedure sp_grantsuser
go

set quoted_identifier on
go

create procedure sp_grant (@PERMISSION   varchar ( 9),
                           @OBJECT_TYPE  varchar ( 9),
                           @GRANTEE      varchar (30),
                           @REVOKE_FLAG  char (1)     = NULL)  as

   set ansinull                      on
   set close on endtran              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_grant CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring   varchar (255),
           @errorchar   varchar (  4),
           @now         char (  8),
           @printcnt    varchar (  7),
           @protect     varchar ( 20),
           @protectcnt  int,
           @objtype     tinyint,
           @retval      int,
           @roleid      int

   create table #grant
      (name   varchar ( 30)  not null,
       txt    varchar (100)  not null)

   declare grant_cursor cursor for
      select txt
      from #grant
      order by name
   for read only

   select @protectcnt = 0,
          @retval     = 0

   select @roleid = srid
   from master.dbo.syssrvroles
   where name = @GRANTEE

   if (upper (@PERMISSION) not in ('ALL', 'DELETE', 'EXEC', 'EXECUTE', 'INSERT',
                                   'SELECT', 'REFERENCE', 'UPDATE'))
      begin
         print '    THE PERMISSION ARGUMENT (%1!) IS INVALID.', @PERMISSION

         print ' '

         print '    VALID ARGUMENTS: all, delete, exec(ute), insert, select, reference, OR update.'

         print ' '

         select @retval = 1
      end

   if (upper (@OBJECT_TYPE) not in ('PROC', 'PROCEDURE', 'TABLE', 'VIEW'))
      begin
         print '    THE OBJECT TYPE ARGUMENT (%1!) IS INVALID.', @OBJECT_TYPE

         print ' '

         print '    VALID ARGUMENTS: proc(edure), table, OR view.'

         print ' '

         select @retval = 1
      end

   if ((not exists (select *
                    from sysusers
                    where name = @GRANTEE))  and
       (@roleid IS NOT NULL))

      begin
         print '    THE GRANTEE ARGUMENT (%1!) DOES NOT EXIST.', @GRANTEE

         print ' '

         select @retval = 1
      end

   if (@retval = 1)
      begin
         return 1
      end

   if (upper (@OBJECT_TYPE) in ('PROC', 'PROCEDURE'))
      begin
         if (upper (@PERMISSION) not in ('EXEC', 'EXECUTE'))
            begin
               print '    THE OBJECT TYPE (%1!) AND PERMISSION (%2!) ARE INCOMPATIBLE.',
                      @OBJECT_TYPE, @PERMISSION

               print ' '

               select @retval = 1
            end
      end
   else if (upper (@PERMISSION) in ('EXEC', 'EXECUTE'))
      begin
         print '    THE OBJECT TYPE (%1!) AND PERMISSION (%2!) ARE INCOMPATIBLE.',
                @OBJECT_TYPE, @PERMISSION

         print ' '

         select @retval = 1
      end

   if ((upper (@PERMISSION) = 'REFERENCE')  and  (upper (@OBJECT_TYPE) = 'VIEW'))
      begin
         print '    THE OBJECT TYPE (%1!) AND PERMISSION (%2!) ARE INCOMPATIBLE.',
                @OBJECT_TYPE, @PERMISSION

         print ' '

         select @retval = 1
      end
   else if ((@REVOKE_FLAG IS NOT NULL)  and  (@roleid < 32))
      begin
         print '    sp_grant DOES NOT REVOKE PERMISSIONS FROM SYSTEM ROLES (%1!).',
               @GRANTEE

         print ' '

         select @retval = 1
      end

   if (@retval = 1)
      begin
         return 1
      end

   if (@REVOKE_FLAG IS NULL)
      begin
         select @protect = 'grant '  +  @PERMISSION  +  ' on '
      end
   else
      begin
         select @protect = 'revoke '  +  @PERMISSION  +  ' on '
      end

   select @objtype = case  upper (@OBJECT_TYPE)
                        when 'TABLE'  then 3
                        when 'VIEW'   then 2
                        else               4
                     end

   print '    COMMANDS OF THE FORM %1!<%2!> to %3! WILL BE EXECUTED.',
         @protect, @OBJECT_TYPE, @GRANTEE

   print ' '

   insert into #grant
      select name,
             @protect  +  '"'  +  name  +  '" to '  +  @GRANTEE
      from sysobjects
      where (sysstat & 15)    = @objtype
        and uid               = user_id ()
        and valid_name (name) = 0

   insert into #grant
      select name,
             @protect  +  name  +  ' to '  +  @GRANTEE
      from sysobjects
      where (sysstat & 15)    = @objtype
        and uid               = user_id ()
        and valid_name (name) > 0

   if (exists (select *
               from #grant))
      begin
         open grant_cursor

         fetch grant_cursor into @cmdstring

         while (@@sqlstatus = 0)
            begin
               execute  (@cmdstring)

               select @protectcnt = @protectcnt + 1

               if ((@protectcnt % 150) = 0)
                  begin
                     select @now      = convert (char (8), getdate (), 8),
                            @printcnt = substring (convert (char (10),
                                                            convert (money,
                                                                     @protectcnt),
                                                            1),
                                                   1,
                                                   7)

                     print '    %1! PERMISSION COMMANDS EXECUTED (%2!).',
                           @printcnt, @now
                  end

               fetch grant_cursor into @cmdstring
            end

         print ' '

         if (@@sqlstatus = 2)
            begin
               select @printcnt = ltrim (substring (convert (char (10),
                                                             convert (money,
                                                                      @protectcnt),
                                                             1),
                                                    1,
                                                    7))

               print '    %1! PERMISSION COMMANDS WERE EXECUTED.', @printcnt
            end
         else
            begin
               select @errorchar = convert (varchar (4), @@sqlstatus)

               print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

               select @retval = 1
            end

         close grant_cursor

         deallocate cursor grant_cursor
      end
   else
      begin
         print '    THERE ARE NO OBJECTS OF TYPE %1!.', @OBJECT_TYPE
      end

   print ' '

   return @retval
go
sp_addcomment sp_grant, 'Grants PERMISSION on all OBJECT TYPE to GRANTEE'
go
sp_addcomment sp_grant, '   if REVOKE_FLAG is non-null, Revoke instead of Grant'
go
sp_procxmode sp_grant, anymode
go

create procedure sp_grantsobjdetail (@OBJECTID    int,
                                     @COLUMN_FLAG varchar (1))
                 with recompile as

   set ansinull                      off              -- Non-ANSI
   set flushmessage                  on
   set string_rtruncation            on

   declare @accesslen    tinyint,
           @cmdstring    varchar (255),
           @collen       tinyint,
           @fromlen      tinyint,
           @maxcolid     tinyint,
           @objtext      varchar (30),
           @ownname      varchar (30),
           @sequence     tinyint,
           @sysstat      smallint,
           @ugrlen       tinyint

   create table #grntsobjdtl
      (uid          smallint      null,
       action       smallint      not null,
       protecttype  tinyint       not null,
       access       varchar (17)  not null,
       colid        smallint      not null,
       coltext      varchar (32)  not null,
       grantor      smallint      null,
       sort         varchar (115) not null)

   select @objtext = name,
          @ownname = user_name (uid),
          @sysstat = (sysstat & 15)
   from sysobjects
   where id = @OBJECTID

   if (@sysstat = 4)
      begin
         insert into #grntsobjdtl
            select p.uid,
                   0,
                   0,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   0,
                   ' ',
                   p.grantor,
                   ' '
         from sybsystemprocs.dbo.usptvalues vTA,
              sybsystemprocs.dbo.usptvalues vTP,
              sysprotects p
         where p.id       = @OBJECTID
           and vTP.type   = 'TP'
           and vTP.number = p.protecttype
           and vTA.type   = 'TA'
           and vTA.number = p.action

         select @accesslen = max (char_length (access)),
                @ugrlen    = max (char_length (user_name (uid))),
                @fromlen   = max (char_length (user_name (grantor)))
         from #grntsobjdtl

         select @cmdstring = 'select ACCESS = substring (access, 1,'
                             +  convert (varchar (2), @accesslen)
                             +  '),'
                             +  '''USER/GROUP/ROLE'' = substring (isnull (user_name (uid), ''?''), 1,'
                             +  convert (varchar (2), @ugrlen)
                             +  '),'
                             +  '''FROM'' = substring (isnull (user_name (grantor), ''?''), 1,'
                             +  convert (varchar (2), @fromlen)
                             +  ')'
                             +  ' from #grntsobjdtl'
                             +  ' order by user_name (uid), access'

         execute  (@cmdstring)
      end
   else
      begin
         --  COLUMN INDEPENDENT PERMISSIONS

         insert into #grntsobjdtl
            select p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   0,
                   '(all)',
                   p.grantor,
                   ' '
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where o.id              = @OBJECTID
              and (o.sysstat & 15) in (1, 2, 3)
              and p.id              = @OBJECTID
              and p.columns        IS NULL
              and vTP.type          = 'TP'
              and vTP.number        = p.protecttype
              and vTA.type          = 'TA'
              and vTA.number        = p.action

         --  COLUMN LEVEL PERMISSIONS

         insert into #grntsobjdtl
            select p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   vP.number,
                   substring (col_name (p.id, vP.number), 1, 32),
                   p.grantor,
                   ' '
            from sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where o.id                        = @OBJECTID
              and (o.sysstat & 15)           in (1, 2, 3)
              and p.id                        = @OBJECTID
              and vTP.type                    = 'TP'
              and vTP.number                  = p.protecttype
              and vTA.type                    = 'TA'
              and vTA.number                  = p.action
              and vP.type                     = 'P'
              and col_name (p.id, vP.number) IS NOT NULL
              and (0x01 & convert (tinyint, substring (p.columns, 1, 1)))
                                              = 0
              and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0x01), vP.aux1, 1)))
                                             <> 0

         --  COLUMN LEVEL PERMISSIONS FOR ALL COLUMNS OF AN OBJECT

         insert into #grntsobjdtl
            select p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   vP.number,
                   substring (isnull (col_name (p.id, vP.number), '(all)'), 1, 32),
                   p.grantor,
                   ' '
            from sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where o.id                         = @OBJECTID
              and (o.sysstat & 15)            in (1, 2, 3)
              and p.id                         = @OBJECTID
              and vTP.type                     = 'TP'
              and vTP.number                   = p.protecttype
              and vTA.type                     = 'TA'
              and vTA.number                   = p.action
              and vP.type                      = 'P'
              and substring (p.columns, 2, 1) IS NULL
              and (0xfe & convert (tinyint, substring (p.columns, 1, 1)))
                                               = 0
              and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0x01), vP.aux1, 1)))
                                              <> 0

         --  MYSTERY CODE DERIVED FROM sp_helprotect

         insert into #grntsobjdtl
            select p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   0,
                   '(all)',
                   p.grantor,
                   ' '
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where o.id              = @OBJECTID
              and (o.sysstat & 15) in (1, 2, 3)
              and p.id              = @OBJECTID
              and p.columns        IS NULL
              and vTP.type          = 'TP'
              and vTP.number        = p.protecttype
              and vTA.type          = 'TA'
              and vTA.number        = p.action
              and p.columns        IS NOT NULL
              and 0x01 & convert (tinyint, substring (p.columns, 1, 1))
                                   <> 0
              and ((substring (p.columns, 2, 1) IS NOT NULL)  or
                   (0xfe & convert (tinyint, substring (p.columns, 1, 1)))
                                   <> 0)

         insert into #grntsobjdtl
            select distinct uid,
                            sum (action),
                            protecttype,
                            'Grant All',
                            colid,
                            coltext,
                            grantor,
                            ' '
            from #grntsobjdtl
            group by uid, protecttype, coltext

         delete from #grntsobjdtl
         where action not in (541, 932)
           and access      = 'Grant All'

         delete from #grntsobjdtl
         where action  = 541
           and coltext = '(all)'

         delete from #grntsobjdtl
         where action   = 932
           and coltext <> '(all)'

         delete #grntsobjdtl
         from #grntsobjdtl g
         where g.action <> 541
           and exists (select *
                       from #grntsobjdtl g2
                       where g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.colid       = g.colid
                         and g2.action      = 541)

         delete #grntsobjdtl
         from #grntsobjdtl g
         where g.action <> 932
           and exists (select *
                       from #grntsobjdtl g2
                       where g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.colid       = g.colid
                         and g2.action      = 932)

         update #grntsobjdtl
         set access = 'Grant* All'
         where access      = 'Grant All'
           and protecttype = 0

         update #grntsobjdtl
         set access = 'Revoke All'
         where access      = 'Grant All'
           and protecttype = 2

         if (@COLUMN_FLAG IS NULL)      -- CONCATENATE COLUMNS
            begin
               select @maxcolid = max (colid),
                      @sequence = 1
               from #grntsobjdtl

               update #grntsobjdtl
               set colid = -1
               from #grntsobjdtl g
               where g.colid > 0
                 and g.colid = (select min (g2.colid)
                                from #grntsobjdtl g2
                                where g2.uid         = g.uid
                                  and g2.protecttype = g.protecttype
                                  and g2.action      = g.action
                                  and g2.grantor     = g.grantor)

               while (@sequence <= @maxcolid)
                  begin
                     update #grntsobjdtl
                     set coltext = substring (g.coltext + ', ' + g2.coltext, 1, 32)
                     from #grntsobjdtl g,
                          #grntsobjdtl g2
                     where g.colid        = -1
                       and g2.uid         = g.uid
                       and g2.protecttype = g.protecttype
                       and g2.action      = g.action
                       and g2.grantor     = g.grantor
                       and g2.colid       = @sequence

                     select @sequence = @sequence + 1
                  end

               delete from #grntsobjdtl
               where colid > 0
            end

         update #grntsobjdtl
         set coltext = '-'
         where coltext = '(all)'
           and ((access like '%Delete%')  or
                (access like '%Execute%')  or
                (access like '%Insert%')  or
                (access like '%All%'))

         update #grntsobjdtl
         set sort = user_name (uid) + ' ' + user_name (grantor) + ' ' + access + coltext

         update #grntsobjdtl
         set uid     = NULL,
             grantor = NULL
         from #grntsobjdtl g
         where exists (select *
                       from #grntsobjdtl g2
                       where g2.uid                       = g.uid
                         and g2.grantor                   = g.grantor
                         and g2.access                    = g.access
                         and g2.coltext                   = g.coltext
                         and g2.access + ' ' + g2.coltext > (select min (g3.access + ' ' + g3.coltext)
                                                             from #grntsobjdtl g3
                                                             where g3.uid     = g2.uid
                                                               and g3.grantor = g2.grantor))

         update #grntsobjdtl
         set uid = NULL
         from #grntsobjdtl g
         where uid     IS NOT NULL
           and grantor IS NOT NULL
           and exists (select *
                       from #grntsobjdtl g2
                       where g2.uid                   = g.uid
                         and g2.grantor               = g.grantor
                         and g2.access                = g.access
                         and g2.coltext               = g.coltext
                         and user_name (g2.uid) + ' '
                             + g2.access + ' '
                             + g2.coltext             > (select min (user_name (g3.uid) + ' '
                                                                     + g3.access + ' '
                                                                     + g3.coltext)
                                                         from #grntsobjdtl g3
                                                         where g3.uid = g2.uid))

         select @ugrlen    = max (char_length (user_name (uid))),
                @fromlen   = max (char_length (user_name (grantor))),
                @accesslen = max (char_length (access)),
                @collen    = max (char_length (coltext))
         from #grntsobjdtl

         select @cmdstring = 'select ''USER/GROUP/ROLE'' = substring (isnull (user_name (uid), '' ''), 1,'
                             +  convert (varchar (2), @ugrlen)
                             +  '),'
                             +  '''FROM'' = substring (isnull (user_name (grantor), '' ''), 1,'
                             +  convert (varchar (2), @fromlen)
                             +  '),'

         if (exists (select *
                     from #grntsobjdtl
                     where charindex ('*', access) > 0))
            begin
               select @cmdstring = @cmdstring
                                   +  '''ACCESS (* = WITH GRANT)'' = substring (access, 1,'

            end
         else
            begin
               select @cmdstring = @cmdstring
                                   +  'ACCESS = substring (access, 1,'
            end

         select @cmdstring = @cmdstring
                             +  convert (varchar (2), @accesslen)
                             +  '),'
                             +  'COLUMNS = substring (coltext, 1,'
                             +  convert (varchar (2), @accesslen)
                             +  ')'
                             +  ' from #grntsobjdtl'
                             +  ' order by sort'

         execute  (@cmdstring)
      end
go
sp_existsprocedure sp_grantsobjdetail
go

create procedure sp_grantsuserdetail (@USER       varchar (30),
                                      @PARMS      varchar (21),
                                      @USER_TYPE  varchar (1))  as

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @attribclass   smallint,        -- class value for roles
           @attribid      smallint,        -- attribute id for roles
           @accesslen     tinyint,
           @cmdstring     varchar (255),
           @column_flag   tinyint,
           @dbname        varchar ( 30),
           @expand_flag   tinyint,
           @groupname     varchar ( 30),
           @groupuid      int,
           @level         tinyint,
           @maxcolid      tinyint,
           @objlen        tinyint,
           @public_flag   tinyint,
           @publicuid     int,
           @sequence      tinyint,
           @suid          int,
           @ugr_title     varchar ( 15),
           @ugrlen        tinyint,
           @uid           int,
           @adjust_flag   tinyint

   create table #roledtl
     (tlevel       tinyint       not null,
      parentid     smallint      not null,
      parentname   varchar (30)  not null,
      roleid       smallint      null,
      rolename     varchar (30)  not null,
      lrid         smallint      not null)

   create table #grntsuserdtl
     (id           int           not null,
      ouid         smallint      not null,
      objtext      varchar (60)  null,
      uid          smallint      not null,
      action       smallint      not null,
      protecttype  tinyint       not null,
      access       varchar (23)  not null,
      colid        smallint      not null,
      coltext      varchar (106) not null,
      username     varchar (30)  not null,
      sort         varchar (115) not null)

   select @column_flag = charindex ('CO', upper (@PARMS)),
          @expand_flag = charindex ('EX', upper (@PARMS)),
          @public_flag = charindex ('PU', upper (@PARMS))

   if ((@PARMS IS NOT NULL)  and  (@column_flag = 0)  and
       (@expand_flag = 0)    and  (@public_flag = 0))
      begin
         print '    THE @PARMS VALUE OF 1! IS MEANINGLESS AND NON-NULL.',
               @PARMS

         print ' '

         print '    VALID ARGUMENT SUBSTRINGS ARE "CO[LUMN]", "EX[PAND]", AND "PU[BLIC]".'

         print ' '
      end
   else
      begin
         if (@column_flag > 0)
            begin
               print '    THERE WILL BE SEPARATE ROWS FOR EACH COLUMN PROTECTION.'

               print ' '
            end

         if (@public_flag > 0)
            begin
               print '    PROTECTIONS FOR public WILL BE LISTED.'

               print ' '
            end
      end

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

   if (@USER_TYPE = 'G')                                    -- Group
      begin
         select @groupuid = uid
         from sysusers
         where name = @USER
           and uid  = gid
      end
   else if (@USER_TYPE = 'R')                               -- Role
      begin
         if (@expand_flag > 0)
            begin
               print '    PROTECTIONS FOR ROLES SUBORDINATE TO %1! WILL BE LISTED.',
                     @USER

               print ' '
            end

         insert into #roledtl
            select 0, 0, ' ', role_id (@USER), @USER, r.lrid
            from master.dbo.syssrvroles sr,
                 sysroles r
            where sr.name = @USER
              and r.id    = sr.srid
      end
   else                                                     -- User
      begin
         select @suid = suid,
                @uid  = uid
         from sysusers
         where name = @USER

         select @groupname = ug.name,
                @groupuid  = ug.uid
         from sysusers ug,
              sysusers uu
         where uu.uid   = @uid
           and ug.uid   = uu.gid
           and ug.name <> 'public'

         insert into #roledtl
            select 0, 0, ' ', sr.srid, sr.name, r.lrid
            from master.dbo.sysloginroles lr,
                 master.dbo.syssrvroles sr,
                 sysroles r
            where lr.suid = @suid
              and sr.srid = lr.srid
              and r.id    = sr.srid
      end

   if ((@USER_TYPE = 'U')  or  (@expand_flag > 0))
      begin
         --  LOAD THE GLOBALLY DEFINED SUBORDINATE ROLES INTO #roledtl

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

               select @level = @level + 1
            end

         update #roledtl
         set lrid = r.lrid
         from sysroles r
         where roleid = r.id

         --  LET'S AVOID DUPLICATE ROLES.

         delete #roledtl
         from #roledtl r1
         where exists (select r2.lrid
                       from #roledtl r2
                       where r2.lrid   = r1.lrid
                         and r2.tlevel = r1.tlevel
                         and r2.tlevel > (select min (r3.tlevel)
                                          from #roledtl r3
                                          where r3.lrid = r2.lrid))
      end

   update #roledtl
   set rolename = rolename + ' #'
   from master.dbo.syssrvroles sr,
        #roledtl dr
   where dr.roleid        = sr.srid
     and sr.password IS NOT NULL

   --  LOAD THE ROLE PROTECTIONS INTO #grantsusrdtl

   --  COLUMN INDEPENDENT PERMISSIONS

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             0,
             '(all)',
             d.rolename,
             ' '
      from sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p,
           #roledtl d
      where p.uid             = d.lrid
        and p.columns        IS NULL
        and o.id              = p.id
        and (o.sysstat & 15) in (1, 2, 3)
        and vTP.type          = 'TP'
        and vTP.number        = p.protecttype
        and vTA.type          = 'TA'
        and vTA.number        = p.action

   --  COLUMN LEVEL PERMISSIONS

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             vP.number,
             col_name (p.id, vP.number),
             d.rolename,
             ' '
      from sybsystemprocs.dbo.usptvalues vP,
           sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p,
           #roledtl d
      where p.uid                       = d.lrid
        and o.id                        = p.id
        and (o.sysstat & 15)           in (1, 2, 3)
        and vTP.type                    = 'TP'
        and vTP.number                  = p.protecttype
        and vTA.type                    = 'TA'
        and vTA.number                  = p.action
        and vP.type                     = 'P'
        and col_name (p.id, vP.number) IS NOT NULL
        and (0x01 & convert (tinyint, substring (p.columns, 1, 1)))
                                        = 0
        and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0x01), vP.aux1, 1)))
                                       <> 0

   --  COLUMN LEVEL PERMISSIONS FOR ALL COLUMNS OF AN OBJECT

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             vP.number,
             isnull (col_name (p.id, vP.number), '(all)'),
             d.rolename,
             ' '
      from sybsystemprocs.dbo.usptvalues vP,
           sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p,
           #roledtl d
      where p.uid                        = d.lrid
        and o.id                         = p.id
        and (o.sysstat & 15)            in (1, 2, 3)
        and vTP.type                     = 'TP'
        and vTP.number                   = p.protecttype
        and vTA.type                     = 'TA'
        and vTA.number                   = p.action
        and vP.type                      = 'P'
        and substring (p.columns, 2, 1) IS NULL
        and (0xfe & convert (tinyint, substring (p.columns, 1, 1)))
                                         = 0
        and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0xfe), vP.aux1, 1)))
                                        <> 0

   --  LOAD THE USER/GROUP/public PROTECTIONS INTO #grantsusrdtl

   select @publicuid = uid
   from sysusers
   where @public_flag > 0
     and name         = 'public'

   --  COLUMN INDEPENDENT PERMISSIONS

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             0,
             '(all)',
             user_name (p.uid),
             ' '
      from sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p
      where p.uid            in (@publicuid, @uid, @groupuid)
        and p.columns        IS NULL
        and o.id              = p.id
        and (o.sysstat & 15) in (1, 2, 3)
        and vTP.type          = 'TP'
        and vTP.number        = p.protecttype
        and vTA.type          = 'TA'
        and vTA.number        = p.action

   --  COLUMN LEVEL PERMISSIONS

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             vP.number,
             col_name (p.id, vP.number),
             user_name (p.uid),
             ' '
      from sybsystemprocs.dbo.usptvalues vP,
           sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p
      where p.uid                      in (@publicuid, @uid, @groupuid)
        and o.id                        = p.id
        and (o.sysstat & 15)           in (1, 2, 3)
        and vTP.type                    = 'TP'
        and vTP.number                  = p.protecttype
        and vTA.type                    = 'TA'
        and vTA.number                  = p.action
        and vP.type                     = 'P'
        and col_name (p.id, vP.number) IS NOT NULL
        and (0x01 & convert (tinyint, substring (p.columns, 1, 1)))
                                        = 0
        and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0x01), vP.aux1, 1)))
                                       <> 0

   --  COLUMN LEVEL PERMISSIONS FOR ALL COLUMNS OF AN OBJECT

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             vP.number,
             isnull (col_name (p.id, vP.number), '(all)'),
             user_name (p.uid),
             ' '
      from sybsystemprocs.dbo.usptvalues vP,
           sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p
      where p.uid                       in (@publicuid, @uid, @groupuid)
        and o.id                         = p.id
        and (o.sysstat & 15)            in (1, 2, 3)
        and vTP.type                     = 'TP'
        and vTP.number                   = p.protecttype
        and vTA.type                     = 'TA'
        and vTA.number                   = p.action
        and vP.type                      = 'P'
        and substring (p.columns, 2, 1) IS NULL
        and (0xfe & convert (tinyint, substring (p.columns, 1, 1)))
                                         = 0
        and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0xfe), vP.aux1, 1)))
                                        <> 0

   if (exists (select *
               from #grntsuserdtl))
      begin
         --  CONVERT delete/insert/select/references/update TO "All"
         --  541 = select + update + references (ON COLUMNS)
         --  932 = select + insert + delete + update + references (ON TABLES/VIEWS)

         insert into #grntsuserdtl
            select distinct id,
                            ouid,
                            objtext,
                            uid,
                            sum (action),
                            protecttype,
                            'Grant All',
                            colid,
                            coltext,
                            username,
                            ' '
            from #grntsuserdtl
            group by id, uid, protecttype, coltext

         delete from #grntsuserdtl
         where action not in (541, 932)
           and access      = 'Grant All'

         delete from #grntsuserdtl
         where action  = 541
           and coltext = '(all)'

         delete from #grntsuserdtl
         where action   = 932
           and coltext <> '(all)'

         delete #grntsuserdtl
         from #grntsuserdtl g
         where g.action <> 541
           and exists (select *
                       from #grntsuserdtl g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.coltext     = g.coltext
                         and g2.action      = 541)

         delete #grntsuserdtl
         from #grntsuserdtl g
         where g.action <> 932
           and exists (select *
                       from #grntsuserdtl g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.coltext     = g.coltext
                         and g2.action      = 932)

         update #grntsuserdtl
         set access = 'Grant* All'
         where access      = 'Grant All'
           and protecttype = 0

         update #grntsuserdtl
         set access = 'Revoke All'
         where access      = 'Grant All'
           and protecttype = 2

         --  END OF CONVERSION TO "All"

         if (@column_flag = 0)          -- CONCATENATE COLUMNS
            begin
               select @maxcolid = max (colid),
                      @sequence = 1
               from #grntsuserdtl

               update #grntsuserdtl
               set colid = -1
               from #grntsuserdtl g
               where g.colid > 0
                 and g.colid = (select min (g2.colid)
                                from #grntsuserdtl g2
                                where g2.id          = g.id
                                  and g2.ouid        = g.ouid
                                  and g2.uid         = g.uid
                                  and g2.protecttype = g.protecttype
                                  and g2.action      = g.action)

               while (@sequence <= @maxcolid)
                  begin
                     update #grntsuserdtl
                     set coltext = substring (g.coltext + ', ' + g2.coltext, 1, 106)
                     from #grntsuserdtl g,
                          #grntsuserdtl g2
                     where g.colid        = -1
                       and g2.id          = g.id
                       and g2.ouid        = g.ouid
                       and g2.uid         = g.uid
                       and g2.protecttype = g.protecttype
                       and g2.action      = g.action
                       and g2.colid       = @sequence

                     select @sequence = @sequence + 1
                  end

               delete from #grntsuserdtl
               where colid > 0
            end
      end

   --  LOAD THE CREATE/DUMP/PROXY PROTECTIONS.

   insert into #grntsuserdtl
      select p.id,
             -1,
             NULL,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             0,
             '-',
             d.rolename,
             ' '
      from sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysprotects p,
           #roledtl d
      where p.uid       = d.lrid
        and p.action   in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)
        and vTP.type    = 'TP'
        and vTP.number  = p.protecttype
        and vTA.type    = 'TA'
        and vTA.number  = p.action

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             0,
             '-',
             d.rolename,
             ' '
      from sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p,
           #roledtl d
      where p.uid             = d.lrid
        and o.id              = p.id
        and (o.sysstat & 15)  = 4
        and vTP.type          = 'TP'
        and vTP.number        = p.protecttype
        and vTA.type          = 'TA'
        and vTA.number        = p.action

   insert into #grntsuserdtl
      select p.id,
             -1,
             NULL,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             0,
             '-',
             user_name (p.uid),
             ' '
      from sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysprotects p
      where p.uid      in (@publicuid, @uid, @groupuid)
        and p.action   in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)
        and vTP.type    = 'TP'
        and vTP.number  = p.protecttype
        and vTA.type    = 'TA'
        and vTA.number  = p.action

   insert into #grntsuserdtl
      select p.id,
             o.uid,
             o.name,
             p.uid,
             p.action,
             p.protecttype,
             substring (vTP.name + ' ' + vTA.name, 1, 23),
             0,
             '-',
             user_name (p.uid),
             ' '
      from sybsystemprocs.dbo.usptvalues vTA,
           sybsystemprocs.dbo.usptvalues vTP,
           sysobjects o,
           sysprotects p
      where p.uid            in (@publicuid, @uid, @groupuid)
        and o.id              = p.id
        and (o.sysstat & 15)  = 4
        and vTP.type          = 'TP'
        and vTP.number        = p.protecttype
        and vTA.type          = 'TA'
        and vTA.number        = p.action

   if (exists (select *
               from #grntsuserdtl))
      begin
         update #grntsuserdtl
         set objtext = substring (g.objtext + ' [' + user_name (g.ouid) + ']', 1, 60)
         from #grntsuserdtl g
         where g.ouid not in (-1, 1)
            or 1           < (select count (o.uid)
                              from sysobjects o
                              where o.name = g.objtext)

         update #grntsuserdtl
         set objtext = substring (objtext + ' (' + coltext + ')', 1, 60)
         where coltext not in ('-', '(all)')

         update #grntsuserdtl
         set sort = substring (isnull (objtext,
                                       char (1))
                               +  space (2)
                               +  access
                               +  space (2)
                               +  username,
                               1,
                               115)

         update #grntsuserdtl
         set access  = ' ',
             objtext = ' '
         from #grntsuserdtl g
         where exists (select g2.access
                       from #grntsuserdtl g2
                       where g2.access   = g.access
                         and g2.objtext  = g.objtext
                         and g2.username = g.username
                         and g2.username > (select min (g3.username)
                                            from #grntsuserdtl g3
                                            where g3.access  = g2.access
                                              and g3.objtext = g2.objtext))

         --  ELIMINATE THE "null value eliminated in set function" WARNING
         --  MESSAGE AND THE USE OF isnull FOR PRINTING.

         update #grntsuserdtl
         set objtext = '-'
         where objtext IS NULL

         if (@USER_TYPE = 'G')
            begin
               select @ugr_title = 'GROUP'
            end
         else if (@USER_TYPE = 'R')
            begin
               select @ugr_title = 'ROLE'
            end
         else
            begin
               select @ugr_title = 'USER/GROUP/ROLE'
            end

         select @accesslen = max (char_length (access)),
                @objlen    = max (char_length (objtext)),
                @ugrlen    = max (char_length (username))
         from #grntsuserdtl

         if (@ugrlen < char_length (@ugr_title))
            begin
               select @ugrlen = char_length (@ugr_title)
            end

         while ((@accesslen + @objlen + @ugrlen) > 75)
            begin
               select @adjust_flag = 1

               if (@accesslen > 17)
                  begin
                     select @accesslen = @accesslen - 1
                  end

               if (@objlen > 35)
                  begin
                     select @objlen = @objlen - 3
                  end

               if (@ugrlen > 15)
                  begin
                     select @ugrlen = @ugrlen - 1
                  end
            end

         if (@adjust_flag = 1)
            begin
               select @objlen = 75 - @accesslen - @ugrlen
            end

         select @cmdstring = 'select ACCESS = substring (access, 1,'
                             +  convert (varchar (2), @accesslen)
                             +  '),'
                             +  'OBJECT = substring (objtext, 1,'
                             +  convert (varchar (2), @objlen)
                             +  '),'
                             +  ''''
                             +  @ugr_title
                             +  ''''
                             +  ' = substring (username, 1,'
                             +  convert (varchar (2), @ugrlen)
                             +  ')'
                             +  ' from #grntsuserdtl'
                             +  ' order by sort'

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

         if (@USER_TYPE = 'G')
            begin
               print '    THERE ARE NO PROTECTIONS FOR GROUP %1! IN DATABASE %2!.',
                     @USER, @dbname
            end
         else if (@USER_TYPE = 'R')
            begin
               print '    THERE ARE NO PROTECTIONS FOR ROLE %1! IN DATABASE %2!.',
                     @USER, @dbname
            end
         else
            begin
               print '    THERE ARE NO PROTECTIONS FOR USER %1! IN DATABASE %2!.',
                     @USER, @dbname
            end

         print ' '
      end
go
sp_existsprocedure sp_grantsuserdetail
go

create procedure sp_grants (@OBJECT      varchar (63),
                            @COLUMN_FLAG varchar (1)  = NULL) as

   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @objectid  int

   select @objectid = object_id (@OBJECT)

   if (@objectid IS NULL)
      begin
         print '    OBJECT %1! DOES NOT EXIST.', @OBJECT

         print ' '
      end
   else
      begin
         if (exists (select *
                     from sysprotects
                     where id = @objectid))
            begin
               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 syscolumns

                     update index statistics sysobjects

                     update index statistics sysprotects

                     update index statistics sysusers
                  end

               execute  sp_grantsobjdetail  @OBJECTID=@objectid,
                                            @COLUMN_FLAG=@COLUMN_FLAG
            end
         else
            begin
               print '    THERE ARE NO PROTECTIONS ON %1!.', @OBJECT

               print ' '
            end
      end
go
sp_addcomment sp_grants, 'Lists the explicit protections for an OBJECT'
go
sp_addcomment sp_grants, '   if COLUMN_FLAG is null (default) do not list individual columns'
go
sp_procxmode sp_grants, anymode
go

create procedure sp_grantsall (@COLUMN_FLAG varchar (1) = NULL) as
   set ansinull                      off              -- Non-ANSI
   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_grantsall CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @accesslen  tinyint,
           @cmdstring  varchar (255),
           @maxcolid   tinyint,
           @objlen     tinyint,
           @sequence   tinyint,
           @ugrlen     tinyint

   create table #grntsall
      (id           int           not null,
       ouid         smallint      not null,
       objtext      varchar (60)  null,
       uid          smallint      not null,
       action       smallint      not null,
       protecttype  tinyint       not null,
       access       varchar (23)  not null,
       colid        smallint      not null,
       coltext      varchar (46)  not null)

   if (exists (select *
               from sysprotects))
      begin
         --  COLUMN INDEPENDENT PERMISSIONS

         insert into #grntsall
            select p.id,
                   o.uid,
                   o.name,
                   p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   0,
                   '-'
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15) in (1, 2, 3)
              and p.id              = o.id
              and p.columns        IS NULL
              and vTP.type          = 'TP'
              and vTP.number        = p.protecttype
              and vTA.type          = 'TA'
              and vTA.number        = p.action

         --  COLUMN LEVEL PERMISSIONS

         insert into #grntsall
            select p.id,
                   o.uid,
                   o.name,
                   p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   vP.number,
                   col_name (p.id, vP.number)
            from sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15)           in (1, 2, 3)
              and p.id                        = o.id
              and vTP.type                    = 'TP'
              and vTP.number                  = p.protecttype
              and vTA.type                    = 'TA'
              and vTA.number                  = p.action
              and vP.type                     = 'P'
              and col_name (p.id, vP.number) IS NOT NULL
              and (0x01 & convert (tinyint, substring (p.columns, 1, 1)))
                                              = 0
              and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0x01), vP.aux1, 1)))
                                             <> 0

         --  COLUMN LEVEL PERMISSIONS FOR ALL COLUMNS OF AN OBJECT

         insert into #grntsall
            select p.id,
                   o.uid,
                   o.name,
                   p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   vP.number,
                   isnull (col_name (p.id, vP.number), '(all)')
            from sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15)            in (1, 2, 3)
              and p.id                         = o.id
              and vTP.type                     = 'TP'
              and vTP.number                   = p.protecttype
              and vTA.type                     = 'TA'
              and vTA.number                   = p.action
              and vP.type                      = 'P'
              and substring (p.columns, 2, 1) IS NULL
              and (0xfe & convert (tinyint, substring (p.columns, 1, 1)))
                                               = 0
              and (vP.aux2 & convert (tinyint, substring (isnull (p.columns, 0xfe), vP.aux1, 1)))
                                              <> 0

         -- CONVERT delete/insert/select/references/update TO "All"
         -- 541 = select + update + references (ON COLUMNS)
         -- 932 = select + insert + delete + update + references (ON TABLES/VIEWS)

         insert into #grntsall
            select distinct id,
                            ouid,
                            objtext,
                            uid,
                            sum (action),
                            protecttype,
                            'Grant All',
                            0,
                            coltext
            from #grntsall
            group by id, ouid, uid, protecttype, colid

         delete from #grntsall
         where action not in (541, 932)
           and access      = 'Grant All'

         delete from #grntsall
         where action  = 541
           and coltext = '(all)'

         delete from #grntsall
         where action   = 932
           and coltext <> '(all)'

         delete #grntsall
         from #grntsall g
         where g.action <> 541
           and exists (select *
                       from #grntsall g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.action      = 541)

         delete #grntsall
         from #grntsall g
         where g.action <> 932
           and exists (select *
                       from #grntsall g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.action      = 932)

         update #grntsall
         set access = 'Grant* All'
         where access      = 'Grant All'
           and protecttype = 0

         update #grntsall
         set access = 'Revoke All'
         where access      = 'Grant All'
           and protecttype = 2

         -- END OF CONVERSION TO "All"

         if (@COLUMN_FLAG IS NULL)      -- Concatenate columns
            begin
               select @maxcolid = max (colid),
                      @sequence = 1
               from #grntsall

               update #grntsall
               set colid = -1
               from #grntsall g
               where g.colid > 0
                 and g.colid = (select min (g2.colid)
                                from #grntsall g2
                                where g2.id          = g.id
                                  and g2.ouid        = g.ouid
                                  and g2.uid         = g.uid
                                  and g2.protecttype = g.protecttype
                                  and g2.action      = g.action)

               while (@sequence <= @maxcolid)
                  begin
                     update #grntsall
                     set coltext = substring (g.coltext + ', ' + g2.coltext, 1, 46)
                     from #grntsall g,
                          #grntsall g2
                     where g.colid        = -1
                       and g2.id          = g.id
                       and g2.ouid        = g.ouid
                       and g2.uid         = g.uid
                       and g2.protecttype = g.protecttype
                       and g2.action      = g.action
                       and g2.colid       = @sequence

                     select @sequence = @sequence + 1
                  end

               delete from #grntsall
               where colid > 0
            end

         -- CREATE/DUMP/PROXY PROTECTIONS

         insert into #grntsall
            select p.id,
                   -1,
                   NULL,
                   p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   0,
                   '-'
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysprotects p
            where p.action   in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236)
              and vTP.type    = 'TP'
              and vTP.number  = p.protecttype
              and vTA.type    = 'TA'
              and vTA.number  = p.action

         -- EXECUTE PROTECTIONS

         insert into #grntsall
            select p.id,
                   o.uid,
                   o.name,
                   p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' Execute', 1, 23),
                   0,
                   '-'
            from sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15) = 4
              and p.id             = o.id
              and p.action         = 224
              and vTP.type         = 'TP'
              and vTP.number       = p.protecttype

         update #grntsall
         set objtext = substring (g.objtext + ' [' + user_name (g.ouid) + ']', 1, 60)
         from #grntsall g
         where g.ouid not in (-1, 1)
            or 1       < (select count (o.uid)
                          from sysobjects o
                          where o.name = g.objtext)

         update #grntsall
         set objtext = substring (objtext + ' (' + coltext + ')', 1, 60)
         where coltext not in ('-', '(all)')

         select @accesslen = max (char_length (access)),
                @objlen    = max (char_length (objtext)),
                @ugrlen    = max (char_length (user_name (uid)))
         from #grntsall

         if ((@accesslen + @objlen + @ugrlen) > 75)
            begin
               select @objlen = 75 - @accesslen - @ugrlen
            end

         if (exists (select *
                     from #grntsall
                     where charindex ('*', access) > 0))
            begin
               print '    "*" INDICATES "WITH GRANT".'

               print ' '
            end

         select @cmdstring = 'select ACCESS = substring (access, 1,'
                             +  convert (varchar (2), @accesslen)
                             +  '),'
                             +  'OBJECT = isnull (substring (objtext, 1,'
                             +  convert (varchar (2), @objlen)
                             +  '), ''-''),'
                             +  '''U/G/R'' = substring (user_name (uid), 1,'
                             +  convert (varchar (2), @ugrlen)
                             +  ')'
                             +  ' from #grntsall'
                             +  ' order by OBJECT, ''U/G/R'', ACCESS'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO PROTECTIONS.'

         print ' '
      end
go
sp_addcomment sp_grantsall, 'Lists all explicit grants and revokes'
go
sp_addcomment sp_grantsall, '   if COLUMN_FLAG is null (default) do not list individual columns'
go
sp_procxmode sp_grantsall, anymode
go

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

         print ' '

         return 1
      end

   declare @accesslen   tinyint,
           @cmdstring   varchar (255),
           @grantorlen  tinyint,
           @ugrlen      tinyint

   create table #grntscreate
      (access       varchar (23)  not null,
       ugr          varchar (30)  not null,
       grantor      varchar (30)  not null,
       sort         varchar (80)  not null)

   if (exists (select *
               from sysprotects
               where action in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)))
      begin
         insert into #grntscreate
            select substring (vTP.name + ' ' + vTA.name, 1, 23),
                   isnull (user_name (p.uid), '?'),
                   isnull (user_name (p.grantor), '?'),
                   ' '
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysprotects p
            where p.action   in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)
              and vTP.type    = 'TP'
              and vTP.number  = p.protecttype
              and vTA.type    = 'TA'
              and vTA.number  = p.action

         update #grntscreate
         set sort = access + ' ' + ugr + ' ' + grantor

         update #grntscreate
         set access = ' '
         from #grntscreate g
         where exists (select g2.access
                       from #grntscreate g2
                       where g2.access = g.access
                         and g2.ugr    = g.ugr
                         and g2.ugr    > (select min (g3.ugr)
                                          from #grntscreate g3
                                          where g3.access = g2.access))

         select @accesslen  = max (char_length (access)),
                @ugrlen     = max (char_length (ugr)),
                @grantorlen = max (char_length (grantor))
         from #grntscreate

         select @cmdstring = 'select ACCESS = substring (access, 1,'
                             +  convert (varchar (2), @accesslen)
                             +  '),'
                             +  '''USER/GROUP/ROLE'' = substring (ugr, 1,'
                             +  convert (varchar (2), @ugrlen)
                             +  '),'
                             +  '''FROM'' = substring (grantor, 1,'
                             +  convert (varchar (2), @grantorlen)
                             +  ')'
                             +  ' from #grntscreate'
                             +  ' order by sort'

         execute  (@cmdstring)
      end
   else
      begin
         print '    THERE ARE NO "CREATE" PROTECTIONS.'

         print ' '
      end
go
sp_addcomment sp_grantscreate, 'Lists all explicit create/dump/proxy protections'
go
sp_procxmode sp_grantscreate, anymode
go

create procedure sp_grantsme (@PARMS varchar (21) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   declare @user varchar (30)

   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 sysattributes

         update index statistics syscolumns

         update index statistics sysobjects

         update index statistics sysprotects

         update index statistics sysroles

         update index statistics sysusers
      end

   select @user = user_name ()

   execute  sp_grantsuserdetail  @USER=@user, @PARMS=@PARMS, @USER_TYPE='U'
go
sp_addcomment sp_grantsme, 'Lists the explicit protections for myself with multiple'
go
sp_addcomment sp_grantsme, 'options within string PARMS:'
go
sp_addcomment sp_grantsme, '   CO[LUMNS]: one output row per column protection'
go
sp_addcomment sp_grantsme, '   PU[BLIC]:  protections for "public" will be listed'
go
sp_procxmode sp_grantsme, anymode
go

create procedure sp_grantsuser (@USER  varchar (30),
                                @PARMS varchar (21) = NULL) as
   set ansinull                      on
   set string_rtruncation            on

   print ' '

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

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

         print ' '

         return 1
      end

   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 sysattributes

         update index statistics syscolumns

         update index statistics sysobjects

         update index statistics sysprotects

         update index statistics sysroles

         update index statistics sysusers
      end

   if (exists (select *
          from sysusers
          where name  = @USER
            and gid  <> uid))
      begin
         execute  sp_grantsuserdetail  @USER=@USER, @PARMS=@PARMS, @USER_TYPE='U'
      end
   else if (exists (select *
                    from master.dbo.syssrvroles
                    where name = @USER))
      begin
         execute  sp_grantsuserdetail  @USER=@USER, @PARMS=@PARMS, @USER_TYPE='R'
      end
   else if (exists (select *
                    from sysusers
                    where name = @USER
                      and gid  = uid))
      begin
         execute  sp_grantsuserdetail  @USER=@USER, @PARMS=@PARMS, @USER_TYPE='G'
      end
   else
      begin
         print '    USER/GROUP/ROLE %1! DOES NOT EXIST.', @USER

         print ' '
      end
go
sp_addcomment sp_grantsuser, 'Lists the explicit protections for a USER/GROUP/ROLE'
go
sp_addcomment sp_grantsuser, 'with multiple options within string PARMS:'
go
sp_addcomment sp_grantsuser, '   CO[LUMNS]: one output row per column protection'
go
sp_addcomment sp_grantsuser, '   EX[PAND]:  subordinate role protections will be listed per ROLE'
go
sp_addcomment sp_grantsuser, '   PU[BLIC]:  protections for "public" will be listed'
go
sp_procxmode sp_grantsuser, anymode
go

dump transaction sybsystemprocs with truncate_only
go
