use sybsystemprocs
go

drop procedure sp_grantsobjdetail
go
drop procedure sp_grantsuserdetailprntgrp
go
drop procedure sp_grantsuserdetailprntrole
go
drop procedure sp_grantsuserdetailprntuser
go
drop procedure sp_grantsuserdetail
go
drop procedure sp_grants
go
drop procedure sp_grantsagg
go
drop procedure sp_grantsall
go
drop procedure sp_grantscreate
go
drop procedure sp_grantsme
go
drop procedure sp_grantsmygroup         -- In 11031A release
go
drop procedure sp_grantsuser
go

set quoted_identifier on
go

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

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @maxcolid     tinyint,
           @objtext      varchar (30),
           @ownname      varchar (30),
           @sequence     tinyint,
           @sysstat      smallint

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

   if (@sysstat = 4)
      begin
         select ACCESS            = substring (vTP.name + ' ' + vTA.name, 1, 23),
                'USER/GROUP/ROLE' = substring (isnull (user_name (p.uid), '?'), 1, 26),
                'FROM'            = substring (isnull (user_name (p.grantor), '?'), 1, 26)
         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
         order by user_name (p.uid), vTP.name, vTA.name
      end
   else
      begin
         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)

         --  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 @sequence = max (char_length (access))
         from #grntsobjdtl

         if (exists (select *
                     from #grntsobjdtl
                     where charindex ('*', access) > 0))
            begin
               select 'USER/GROUP/ROLE'         = substring (isnull (user_name (uid), ' '), 1, 15),
                      'FROM'                    = substring (isnull (user_name (grantor), ' '), 1, 11),
                      'ACCESS (* = WITH GRANT)' = access,
                      COLUMNS                   = substring (coltext, 1, 25)
               from #grntsobjdtl
               order by sort
            end
         else if (@sequence > 13)
            begin
               select 'USER/GROUP/ROLE' = substring (isnull (user_name (uid), ' '), 1, 15),
                      'FROM'            = substring (isnull (user_name (grantor), ' '), 1, 14),
                      'ACCESS'          = access,
                      COLUMNS           = substring (coltext, 1, 28)
               from #grntsobjdtl
               order by sort
            end
         else
            begin
               select 'USER/GROUP/ROLE' = substring (isnull (user_name (uid), ' '), 1, 15),
                      'FROM'            = substring (isnull (user_name (grantor), ' '), 1, 14),
                      'ACCESS'          = substring (access, 1, 13),
                      COLUMNS           = substring (coltext, 1, 32)
               from #grntsobjdtl
               order by sort
            end
      end
go
sp_procxmode sp_grantsobjdetail, unchained
go

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

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

create procedure sp_grantsuserdetailprntgrp as

   declare @maxaccesslen  tinyint,
           @maxobjlen     tinyint

   select @maxaccesslen = max (char_length (access)),
          @maxobjlen    = max (char_length (objtext))
   from #grntsuserdtl

   if ((@maxaccesslen < 14)  and  (@maxobjlen < 38))
      begin
         select ACCESS  = substring (access, 1, 13),
                OBJECT  = substring (objtext, 1, 37),
                'GROUP' = substring (username, 1, 25)
         from #grntsuserdtl
         order by sort
      end
   else if (@maxaccesslen < 14)
      begin
         select ACCESS  = substring (access, 1, 13),
                OBJECT  = substring (objtext, 1, 50),
                'GROUP' = substring (username, 1, 12)
         from #grntsuserdtl
         order by sort
      end
   else if (@maxobjlen < 36)
      begin
         select ACCESS  = access,
                OBJECT  = substring (objtext, 1, 35),
                'GROUP' = substring (username, 1, 17)
         from #grntsuserdtl
         order by sort
      end
   else
      begin
         select ACCESS  = access,
                OBJECT  = substring (objtext, 1, 52)
         from #grntsuserdtl
         order by sort
      end
go
sp_procxmode sp_grantsuserdetailprntgrp, unchained
go

create procedure sp_grantsuserdetailprntrole as

   declare @maxaccesslen  tinyint,
           @maxuserlen    tinyint

   select @maxaccesslen = max (char_length (access)),
          @maxuserlen   = max (char_length (username))
   from #grntsuserdtl

   if ((@maxaccesslen < 14)  and  (@maxuserlen < 12))
      begin
         select ACCESS = substring (access, 1, 13),
                OBJECT = substring (objtext, 1, 51),
                'ROLE' = substring (username, 1, 11)
         from #grntsuserdtl
         order by sort
      end
   else if ((@maxaccesslen < 14)  and  (@maxuserlen < 18))
      begin
         select ACCESS = substring (access, 1, 13),
                OBJECT = substring (objtext, 1, 45),
                'ROLE' = substring (username, 1, 17)
         from #grntsuserdtl
         order by sort
      end
   else if (@maxuserlen < 18)
      begin
         select ACCESS = access,
                OBJECT = substring (objtext, 1, 35),
                'ROLE' = substring (username, 1, 17)
         from #grntsuserdtl
         order by sort
      end
   else
      begin
         select ACCESS = access,
                OBJECT = substring (objtext, 1, 30),
                'ROLE' = substring (username, 1, 22)
         from #grntsuserdtl
         order by sort
      end
go
sp_procxmode sp_grantsuserdetailprntrole, unchained
go

create procedure sp_grantsuserdetailprntuser as

   declare @maxaccesslen  tinyint,
           @maxuserlen    tinyint

   select @maxaccesslen = max (char_length (access)),
          @maxuserlen   = max (char_length (username))
   from #grntsuserdtl

   if ((@maxaccesslen < 14)  and  (@maxuserlen < 16))
      begin
         select ACCESS            = substring (access, 1, 13),
                OBJECT            = substring (objtext, 1, 47),
                'USER/GROUP/ROLE' = substring (username, 1, 15)
         from #grntsuserdtl
         order by sort
      end
   else if ((@maxaccesslen < 14)  and  (@maxuserlen < 20))
      begin
         select ACCESS            = substring (access, 1, 13),
                OBJECT            = substring (objtext, 1, 43),
                'USER/GROUP/ROLE' = substring (username, 1, 19)
         from #grntsuserdtl
         order by sort
      end
   else if (@maxuserlen < 18)
      begin
         select ACCESS            = access,
                OBJECT            = substring (objtext, 1, 35),
                'USER/GROUP/ROLE' = substring (username, 1, 17)
         from #grntsuserdtl
         order by sort
      end
   else
      begin
         select ACCESS            = access,
                OBJECT            = substring (objtext, 1, 30),
                'USER/GROUP/ROLE' = substring (username, 1, 22)
         from #grntsuserdtl
         order by sort
      end
go
sp_procxmode sp_grantsuserdetailprntuser, unchained
go

drop table #grntsuserdtl
go

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

   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @attribclass   smallint,        -- class value for roles
           @attribid      smallint,        -- attribute id for roles
           @column_flag   tinyint,
           @dbname        varchar (30),
           @expand_flag   tinyint,
           @groupname     varchar (30),
           @groupuid      int,
           @level         tinyint,
           @maxcolid      tinyint,
           @public_flag   tinyint,
           @publicuid     int,
           @sequence      tinyint,
           @suid          int,
           @uid           int

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

   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
               execute sp_grantsuserdetailprntgrp
            end
         else if (@USER_TYPE = 'R')
            begin
               execute sp_grantsuserdetailprntrole
            end
         else
            begin
               execute sp_grantsuserdetailprntuser
            end
      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_procxmode sp_grantsuserdetail, unchained
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)
                  begin
                     print '    UPDATING STATISTICS ON SYSTEM TABLES.'

                     print ' '

                     update statistics syscolumns

                     update statistics sysobjects

                     update statistics sysprotects

                     update statistics sysusers
                  end

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

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

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

         print ' '

         return 1
      end

   declare @colcount  varchar (10)

   create table #grntsagg
      (id           int            not null,
       ouid         smallint       not null,
       uid          smallint       not null,
       otype        varchar (16)   not null,
       action       smallint       not null,
       protecttype  tinyint        not null,
       access       char (17)      not null,
       colid        smallint       not null,
       coltext      varchar (32)   not null,
       columns      varbinary (32) null,
       sort         varchar (75)   not null)

   if (exists (select *
               from sysprotects))
      begin
         if (exists (select *
                     from sysprotects
                     where action in (167, 198, 203, 207, 221, 222, 228, 233, 235, 236, 253)))
            begin
               print '    THERE ARE CREATE/DUMP/PROXY PROTECTIONS.'

               print ' '

               select distinct ACCESS = substring (vTP.name + ' ' + vTA.name, 1, 23),
                               'COUNT'= count (p.id)
               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
               group by p.action
               having 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
               order by ACCESS
            end
         else
            begin
               print '    THERE ARE NO CREATE/DUMP/PROXY PROTECTIONS.'
            end

         print ' '

         --  COLUMN INDEPENDENT PERMISSIONS

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

         --  COLUMN LEVEL PERMISSIONS

         insert into #grntsagg
            select p.id,
                   o.uid,
                   p.uid,
                   substring (vO.name, 1, 16),
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   vP.number,
                   substring (col_name (p.id, vP.number), 1, 16),
                   p.columns,
                   ' '
            from sybsystemprocs.dbo.usptvalues vO,
                 sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15)           in (1, 2, 3, 4)
              and p.id                        = o.id
              and vO.type                     = 'O'
              and vO.number                   = (o.sysstat & 15)
              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 #grntsagg
            select p.id,
                   o.uid,
                   p.uid,
                   substring (vO.name, 1, 16),
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   vP.number,
                   substring (isnull (col_name (p.id, vP.number), '(all)'), 1, 16),
                   p.columns,
                   ' '
            from sybsystemprocs.dbo.usptvalues vO,
                 sybsystemprocs.dbo.usptvalues vP,
                 sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where (o.sysstat & 15)            in (1, 2, 3, 4)
              and p.id                         = o.id
              and vO.type                      = 'O'
              and vO.number                    = (o.sysstat & 15)
              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

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

         insert into #grntsagg
            select distinct id,
                            ouid,
                            uid,
                            otype,
                            sum (action),
                            protecttype,
                            'Grant All',
                            0,
                            ' ',
                            columns,
                            ' '
            from #grntsagg
            group by id, uid, protecttype, columns

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

         delete #grntsagg
         from #grntsagg g
         where g.action <> 541
           and exists (select *
                       from #grntsagg g2
                       where g2.id          = g.id
                         and g2.uid         = g.uid
                         and g2.protecttype = g.protecttype
                         and g2.columns     = isnull (g.columns, 0x01)
                         and g2.action      = 541)

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

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

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

         update #grntsagg
         set sort = user_name (uid) + ' ' + otype + access

         if (exists (select *
                     from #grntsagg
                     where columns IS NULL
                        or columns  = 0x01))
            begin
               print '    THERE ARE PROTECTIONS ON PROCEDURE(S)/TABLE(S)/VIEW(S).'

               print ' '

               select distinct ugrname = substring (isnull (user_name (uid), '?'), 1, 28),
                               otype,
                               access,
                               C       = substring (convert (char (10), convert (money, count (id)), 1), 1, 7),
                               sort
               into #grntsagg2
               from #grntsagg
               where (columns IS NULL  or  columns  = 0x01)
               group by otype, uid, action, protecttype, sort
               having (columns IS NULL  or  columns  = 0x01)

               update #grntsagg2
               set ugrname = ' ',
                   otype   = ' '
               from #grntsagg2 g
               where exists (select *
                             from #grntsagg2 g2
                             where g2.ugrname = g.ugrname
                               and g2.otype   = g.otype
                               and g2.access  = g.access
                               and g2.access  > (select min (g3.access)
                                                 from #grntsagg2 g3
                                                 where g3.ugrname = g2.ugrname
                                                   and g3.otype   = g2.otype))

               update #grntsagg2
               set ugrname = ' '
               from #grntsagg2 g
               where exists (select *
                             from #grntsagg2 g2
                             where g2.ugrname                 = g.ugrname
                               and g2.otype                   = g.otype
                               and g2.access                  = g.access
                               and g2.otype + ' ' + g2.access > (select min (g3.otype + ' ' + g3.access)
                                                                   from #grntsagg2 g3
                                                                   where g3.ugrname = g2.ugrname))

               if (exists (select *
                           from #grntsagg2
                           where charindex ('*', access) > 0))
                  begin
                     select 'USER/GROUP/ROLE'         = ugrname,
                            'OBJECT TYPE'             = otype,
                            'ACCESS (* = WITH GRANT)' = access,
                            'COUNT'                   = C
                     from #grntsagg2
                     order by sort
                  end
               else
                  begin
                     select 'USER/GROUP/ROLE' = ugrname,
                            'OBJECT TYPE'     = otype,
                            ACCESS            = access,
                            'COUNT'           = C
                     from #grntsagg2
                     order by sort
                  end
            end
         else
            begin
               print '    THERE ARE NO PROTECTIONS ON PROCEDURES OR ENTIRE TABLE(S)/VIEW(S).'
            end

         print ' '

         select @colcount = substring (convert (char (8),
                                                convert (money,
                                                         count (distinct id)),
                                                1),
                                       1,
                                       5)
         from #grntsagg
         where columns IS NOT NULL
           and columns     <> 0x01

         select @colcount = ltrim (@colcount)

         if (@colcount <> '0')
            begin
               print ' '

               if (@colcount = '1')
                  begin
                     print '    THERE ARE COLUMN PROTECTIONS ON ONE OBJECT.'
                  end
               else
                  begin
                     print '    THERE ARE COLUMN PROTECTIONS ON %1! OBJECTS.', @colcount
                  end

               print ' '

               delete from #grntsagg
               where columns IS NULL
                  or columns  = 0x01

                  select 'TABLE/VIEW' = space (30)
                  where 1 = 2
               union
                  select object_name (g.id)
                  from #grntsagg g
                  where g.ouid = 1
                    and 1      = (select count (o.uid)
                                  from sysobjects o
                                  where o.name = object_name (g.id))
               union
                  select substring (object_name (g.id) + ' (' + user_name (g.ouid) + ')', 1, 30)
                  from #grntsagg g
                  where ((g.ouid <> 1)  or
                         (1       < (select count (o.uid)
                                     from sysobjects o
                                     where o.name = object_name (g.id))))
               order by 'TABLE/VIEW'
            end
         else
            begin
               print '    THERE ARE NO PROTECTIONS ON ANY TABLE/VIEW COLUMNS.'
            end
      end
   else
      begin
         print '    THERE ARE NO PROTECTIONS.'
      end

   print ' '
go
sp_addcomment sp_grantsagg, 15, 'Displays protection aggregates'
go
sp_procxmode sp_grantsagg, anymode
go

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

         print ' '

         return 1
      end

   declare @maxcolid  tinyint,
           @sequence  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 @sequence = max (char_length (access))
         from #grntsall

         if (exists (select *
                     from #grntsall
                     where charindex ('*', access) > 0))
            begin
               select 'ACCESS (* = WITH GRANT)' = access,
                      OBJECT                    = isnull (substring (objtext, 1, 40), '-'),
                      'U/G/R'                   = substring (user_name (uid), 1, 12)
               from #grntsall
               order by OBJECT, 'U/G/R', 'ACCESS (* = WITH GRANT)'
            end
         else if (@sequence < 14)
            begin
               select ACCESS       = substring (access, 1, 13),
                      OBJECT       = isnull (substring (objtext, 1, 50), '-'),
                      'U/G/R'      = substring (user_name (uid), 1, 12)
               from #grntsall
               order by OBJECT, 'U/G/R', ACCESS
            end
         else if (@sequence < 17)
            begin
               select ACCESS       = substring (access, 1, 16),
                      OBJECT       = isnull (substring (objtext, 1, 47), '-'),
                      'U/G/R'      = substring (user_name (uid), 1, 12)
               from #grntsall
               order by OBJECT, 'U/G/R', ACCESS
            end
         else
            begin
               select ACCESS       = access,
                      OBJECT       = isnull (substring (objtext, 1, 40), '-'),
                      'U/G/R'      = substring (user_name (uid), 1, 12)
               from #grntsall
               order by OBJECT, 'U/G/R', ACCESS
            end
      end
   else
      begin
         print '    THERE ARE NO PROTECTIONS.'

         print ' '
      end
go
sp_addcomment sp_grantsall, 15, 'Lists all explicit grants and revokes'
go
sp_addcomment sp_grantsall, 15, '   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

   create table #grntscreate
      (access       varchar (23)  not null,
       ugr          varchar (26)  not null,
       grantor      varchar (26)  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),
                   substring (isnull (user_name (p.uid), '?'), 1, 26),
                   substring (isnull (user_name (p.grantor), '?'), 1, 26),
                   ' '
            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 ACCESS            = access,
                'USER/GROUP/ROLE' = ugr,
                'FROM'            = grantor
         from #grntscreate
         order by sort
      end
   else
      begin
         print '    THERE ARE NO "CREATE" PROTECTIONS.'

         print ' '
      end
go
sp_addcomment sp_grantscreate, 15, '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)
      begin
         print '    UPDATING STATISTICS ON SYSTEM TABLES.'

         print ' '

         update statistics sysattributes

         update statistics syscolumns

         update statistics sysobjects

         update statistics sysprotects

         update statistics sysroles

         update statistics sysusers
      end

   select @user = user_name ()

   execute sp_grantsuserdetail @user, @PARMS, 'U'
go
sp_addcomment sp_grantsme, 15, 'Lists the explicit protections for myself (with multiple options)'
go
sp_addcomment sp_grantsme, 15, '   CO[LUMNS]: one output row per column protection'
go
sp_addcomment sp_grantsme, 15, '   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)
      begin
         print '    UPDATING STATISTICS ON SYSTEM TABLES.'

         print ' '

         update statistics sysattributes

         update statistics syscolumns

         update statistics sysobjects

         update statistics sysprotects

         update statistics sysroles

         update statistics sysusers
      end

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

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

dump transaction sybsystemprocs with truncate_only
go
