use sybsystemprocs
go
drop procedure sp_grantsobjdetail
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)) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

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

   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' = 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
         if (@sysstat = 1)
            begin
               select @typemssg = 'SYSTEM TABLE'
            end
         else if (@sysstat = 2)
            begin
               select @typemssg = 'VIEW'
            end
         else if (@sysstat = 3)
            begin
               select @typemssg = 'USER TABLE'
            end
         else
            begin
               select @typemssg = '???'
            end

         print '    %1! IS A %2! OWNED BY %3!.', @objtext, @typemssg, @ownname

         print ' '

         create table #grantsobjectdetail
            (uid          smallint      not 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      not null)

         --  COLUMN INDEPENDENT PERMISSIONS

         insert into #grantsobjectdetail
            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 #grantsobjectdetail
            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 #grantsobjectdetail
            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 #grantsobjectdetail
            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 #grantsobjectdetail
            select distinct uid,
                            sum (action),
                            protecttype,
                            'Grant All',
                            colid,
                            coltext,
                            grantor
            from #grantsobjectdetail
            group by uid, protecttype, coltext

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

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

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

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

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

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

         update #grantsobjectdetail
         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 #grantsobjectdetail

               update #grantsobjectdetail
               set colid = -1
               from #grantsobjectdetail g
               where g.colid > 0
                 and g.colid = (select min (g2.colid)
                                from #grantsobjectdetail g2
                                where g2.colid > 0)

               while (@sequence <= @maxcolid)
                  begin
                     update #grantsobjectdetail
                     set coltext = substring (g.coltext + ', ' + g2.coltext, 1, 32)
                     from #grantsobjectdetail g,
                          #grantsobjectdetail g2
                     where g.colid        = -1
                       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 #grantsobjectdetail
               where colid > 0
            end

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

         select @sequence = max (char_length (access))
         from #grantsobjectdetail

         if (exists (select *
                     from #grantsobjectdetail
                     where charindex ('*', access) > 0))
            begin
               select 'ACCESS (* = WITH GRANT)' = access,
                      COLUMNS                   = substring (coltext, 1, 29),
                      'USER'                    = substring (isnull (user_name (uid), '?'), 1, 14),
                      'FROM'                    = substring (isnull (user_name (grantor), '?'), 1, 14)
               from #grantsobjectdetail
               order by user_name (uid), access, coltext
            end
         else if (@sequence > 13)
            begin
               select 'ACCESS' = access,
                      COLUMNS  = substring (coltext, 1, 29),
                      'USER'   = substring (isnull (user_name (uid), '?'), 1, 14),
                      'FROM'   = substring (isnull (user_name (grantor), '?'), 1, 14)
               from #grantsobjectdetail
               order by user_name (uid), access, coltext
            end
         else
            begin
               select 'ACCESS' = substring (access, 1, 13),
                      COLUMNS  = substring (coltext, 1, 33),
                      'USER'   = substring (isnull (user_name (uid), '?'), 1, 14),
                      'FROM'   = substring (isnull (user_name (grantor), '?'), 1, 14)
               from #grantsobjectdetail
               order by user_name (uid), access, coltext
            end
      end
go
sp_procxmode sp_grantsobjdetail, unchained
go

create procedure sp_grantsuserdetail (@USER        varchar (30),
                                      @GROUP_FLAG  varchar (1),
                                      @COLUMN_FLAG varchar (1)) as
   set ansinull                      on
   set string_rtruncation            on

   declare @groupname  varchar (30),
           @groupuid   int,
           @maxcolid   tinyint,
           @sequence   tinyint,
           @uid        int

   create table #grantsuserdetail
      (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)

   select @uid = user_id (@USER)

   if (@GROUP_FLAG IS NOT NULL)
      begin
         select @groupname = user_name (ug.uid),
                @groupuid  = ug.uid
         from sysusers ug,
              sysusers uu
         where uu.uid = @uid
           and ug.uid = uu.gid
      end

   if (exists (select *
               from sysprotects
               where uid in (@uid, @groupuid)))
      begin
         --  COLUMN INDEPENDENT PERMISSIONS

         insert into #grantsuserdetail
            select p.id,
                   o.uid,
                   o.name,
                   p.uid,
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 23),
                   0,
                   '(all)'
            from sybsystemprocs.dbo.usptvalues vTA,
                 sybsystemprocs.dbo.usptvalues vTP,
                 sysobjects o,
                 sysprotects p
            where p.uid            in (@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 #grantsuserdetail
            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 p.uid                      in (@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 #grantsuserdetail
            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 p.uid                       in (@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

         -- 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 #grantsuserdetail
            select distinct id,
                            ouid,
                            objtext,
                            uid,
                            sum (action),
                            protecttype,
                            'Grant All',
                            colid,
                            coltext
            from #grantsuserdetail
            group by id, uid, protecttype, coltext

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

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

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

         delete #grantsuserdetail
         from #grantsuserdetail g
         where g.action <> 541
           and exists (select *
                       from #grantsuserdetail 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 #grantsuserdetail
         from #grantsuserdetail g
         where g.action <> 932
           and exists (select *
                       from #grantsuserdetail 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 #grantsuserdetail
         set access = 'Grant* All'
         where access      = 'Grant All'
           and protecttype = 0

         update #grantsuserdetail
         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 #grantsuserdetail

               update #grantsuserdetail
               set colid = -1
               from #grantsuserdetail g
               where g.colid > 0
                 and g.colid = (select min (g2.colid)
                                from #grantsuserdetail 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 #grantsuserdetail
                     set coltext = substring (g.coltext + ', ' + g2.coltext, 1, 46)
                     from #grantsuserdetail g,
                          #grantsuserdetail 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 #grantsuserdetail
               where colid > 0
            end

         insert into #grantsuserdetail
            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.uid      in (@uid, @groupuid)
              and p.action   in (198, 203, 207, 222, 228, 233, 235, 236)
              and vTP.type    = 'TP'
              and vTP.number  = p.protecttype
              and vTA.type    = 'TA'
              and vTA.number  = p.action

         insert into #grantsuserdetail
            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 p.uid            in (@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

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

         select @sequence = max (char_length (access))
         from #grantsuserdetail

         if (exists (select *
                     from #grantsuserdetail
                     where charindex ('*', access) > 0))
            begin
                 select 'ACCESS (* = WITH GRANT)' = space (23),
                        OBJECT                    = space (40),
                        'USER'                    = space (12)
                 where 1 = 2
              union
                 select access,
                        isnull (substring (objtext, 1, 40), '-'),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext in ('-', '(all)')
              union
                 select access,
                        substring (objtext + ' (' + coltext + ')', 1, 40),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext not in ('-', '(all)')
              order by OBJECT, 'USER', 'ACCESS (* = WITH GRANT)'
            end
         else if (@sequence < 14)
            begin
                 select ACCESS  = space (13),
                        OBJECT  = space (50),
                        'USER'  = space (12)
                 where 1 = 2
              union
                 select substring (access, 1, 13),
                        isnull (substring (objtext, 1, 50), '-'),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext in ('-', '(all)')
              union
                 select substring (access, 1, 13),
                        substring (objtext + ' (' + coltext + ')', 1, 50),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext not in ('-', '(all)')
              order by OBJECT, 'USER', ACCESS
            end
         else if (@sequence < 17)
            begin
                 select ACCESS  = space (16),
                        OBJECT  = space (47),
                        'USER'  = space (12)
                 where 1 = 2
              union
                 select substring (access, 1, 16),
                        isnull (substring (objtext, 1, 47), '-'),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext in ('-', '(all)')
              union
                 select substring (access, 1, 16),
                        substring (objtext + ' (' + coltext + ')', 1, 47),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext not in ('-', '(all)')
              order by OBJECT, 'USER', ACCESS
            end
         else
            begin
                 select ACCESS = space (23),
                        OBJECT = space (40),
                        'USER' = space (12)
                 where 1 = 2
              union
                 select access,
                        isnull (substring (objtext, 1, 40), '-'),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext in ('-', '(all)')
              union
                 select access,
                        substring (objtext + ' (' + coltext + ')', 1, 40),
                        substring (user_name (uid), 1, 12)
                 from #grantsuserdetail
                 where coltext not in ('-', '(all)')
              order by OBJECT, 'USER', ACCESS
            end
      end
   else if (@groupuid IS NULL)
      begin
         print '    THERE ARE NO PROTECTIONS FOR <%1!>.', @USER

         print ' '
      end
   else
      begin
         print '    THERE ARE NO PROTECTIONS FOR EITHER <%1!> OR <%2!>.',
               @USER, @groupname

         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
               exec 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 #grantsagg
      (id           int            not null,
       ouid         smallint       not null,
       uid          smallint       not null,
       sysstat      tinyint        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)

   if (exists (select *
               from sysprotects))
      begin
         if (exists (select *
                     from sysprotects
                     where action in (198, 203, 207, 222, 228, 233, 235, 236)))
            begin
               print '    THERE ARE CREATE/DUMP 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 (198, 203, 207, 222, 228, 233, 235, 236)
                 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 (198, 203, 207, 222, 228, 233, 235, 236)
                  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 PROTECTIONS.'
            end

         print ' '

         --  COLUMN INDEPENDENT PERMISSIONS

         insert into #grantsagg
            select p.id,
                   o.uid,
                   p.uid,
                   (o.sysstat & 15),
                   p.action,
                   p.protecttype,
                   substring (vTP.name + ' ' + vTA.name, 1, 17),
                   0,
                   '-',
                   NULL
            from 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 vTP.type          = 'TP'
              and vTP.number        = p.protecttype
              and vTA.type          = 'TA'
              and vTA.number        = p.action

         --  COLUMN LEVEL PERMISSIONS

         insert into #grantsagg
            select p.id,
                   o.uid,
                   p.uid,
                   (o.sysstat & 15),
                   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 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 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 #grantsagg
            select p.id,
                   o.uid,
                   p.uid,
                   (o.sysstat & 15),
                   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 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 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 #grantsagg
            select distinct id,
                            ouid,
                            uid,
                            sysstat,
                            sum (action),
                            protecttype,
                            'Grant All',
                            0,
                            ' ',
                            columns
            from #grantsagg
            group by id, uid, protecttype, columns

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

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

         delete #grantsagg
         from #grantsagg g
         where g.action <> 932
           and exists (select *
                       from #grantsagg 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 #grantsagg
         set access = 'Grant* All'
         where access      = 'Grant All'
           and protecttype = 0

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

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

               print ' '

               if (exists (select *
                           from #grantsagg
                           where charindex ('*', access) > 0))
                  begin
                        select 'GROUP/USER'              = space (28),
                               'OBJECT TYPE'             = space (16),
                               'ACCESS (* = WITH GRANT)' = space (23),
                               'COUNT'                   = space (7)
                        where 1 = 2
                     union
                        select substring (isnull (user_name (g.uid), '?'), 1, 28),
                               substring (vO.name, 1, 16),
                               g.access,
                               substring (convert (char (10), convert (money, count (g.id)), 1), 1, 7)
                        from #grantsagg g,
                             sybsystemprocs.dbo.usptvalues vO
                        where (g.columns IS NULL  or  g.columns  = 0x01)
                          and vO.type   = 'O'
                          and vO.number = g.sysstat
                        group by g.sysstat, g.uid, g.action, g.protecttype
                        having (g.columns IS NULL  or  g.columns  = 0x01)
                           and vO.type   = 'O'
                           and vO.number = g.sysstat
                     order by 'GROUP/USER', 'OBJECT TYPE', 'ACCESS (* = WITH GRANT)'
                  end
               else
                  begin
                        select 'USER'        = space (28),
                               'OBJECT TYPE' = space (16),
                               ACCESS        = space (17),
                               'COUNT'       = space (7)
                        where 1 = 2
                     union
                        select substring (isnull (user_name (g.uid), '?'), 1, 28),
                               substring (vO.name, 1, 16),
                               g.access,
                               substring (convert (char (10), convert (money, count (g.id)), 1), 1, 7)
                        from #grantsagg g,
                             sybsystemprocs.dbo.usptvalues vO
                        where (g.columns IS NULL  or  g.columns  = 0x01)
                          and vO.type   = 'O'
                          and vO.number = g.sysstat
                        group by g.sysstat, g.uid, g.action, g.protecttype
                        having (g.columns IS NULL  or  g.columns  = 0x01)
                           and vO.type   = 'O'
                           and vO.number = g.sysstat
                     order by 'USER', 'OBJECT TYPE', ACCESS
                  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 #grantsagg
         where columns IS NOT NULL
           and columns     <> 0x01

         select @colcount = ltrim (@colcount)

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

               print '    THERE ARE COLUMN PROTECTIONS ON %1! OBJECTS.', @colcount

               print ' '

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

                  select 'TABLE/VIEW' = space (30)
                  where 1 = 2
               union
                  select object_name (g.id)
                  from #grantsagg 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 #grantsagg 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.'

               print ' '
            end
      end
   else
      begin
         print '    THERE ARE NO PROTECTIONS.'

         print ' '
      end
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 #grantsall
      (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 #grantsall
            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 #grantsall
            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 #grantsall
            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 #grantsall
            select distinct id,
                            ouid,
                            objtext,
                            uid,
                            sum (action),
                            protecttype,
                            'Grant All',
                            0,
                            coltext
            from #grantsall
            group by id, ouid, uid, protecttype, colid

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

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

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

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

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

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

         update #grantsall
         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 #grantsall

               update #grantsall
               set colid = -1
               from #grantsall g
               where g.colid > 0
                 and g.colid = (select min (g2.colid)
                                from #grantsall 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 #grantsall
                     set coltext = substring (g.coltext + ', ' + g2.coltext, 1, 46)
                     from #grantsall g,
                          #grantsall 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 #grantsall
               where colid > 0
            end

         -- CREATE/DUMP PROTECTIONS

         insert into #grantsall
            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 (198, 203, 207, 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 #grantsall
            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 #grantsall
         set objtext = substring (g.objtext + ' [' + user_name (g.ouid) + ']', 1, 60)
         from #grantsall g
         where g.ouid not in (-1, 1)
            or 1       < (select count (o.uid)
                          from sysobjects o
                          where o.name = g.objtext)

         select @sequence = max (char_length (access))
         from #grantsall

         if (exists (select *
                     from #grantsall
                     where charindex ('*', access) > 0))
            begin
                  select 'ACCESS (* = WITH GRANT)' = space (23),
                         OBJECT                    = space (40),
                         'GROUP/USER'              = space (12)
                  where 1 = 2
               union
                  select access,
                         isnull (substring (objtext, 1, 40), '-'),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext in ('-', '(all)')
               union
                  select access,
                         substring (objtext + ' (' + coltext + ')', 1, 40),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext not in ('-', '(all)')
               order by OBJECT, 'GROUP/USER', 'ACCESS (* = WITH GRANT)'
            end
         else if (@sequence < 14)
            begin
                  select ACCESS       = space (13),
                         OBJECT       = space (50),
                         'GROUP/USER' = space (12)
                  where 1 = 2
               union
                  select substring (access, 1, 13),
                         isnull (substring (objtext, 1, 50), '-'),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext in ('-', '(all)')
               union
                  select substring (access, 1, 13),
                         substring (objtext + ' (' + coltext + ')', 1, 50),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext not in ('-', '(all)')
               order by OBJECT, 'GROUP/USER', ACCESS
            end
         else if (@sequence < 17)
            begin
                  select ACCESS       = space (16),
                         OBJECT       = space (47),
                         'GROUP/USER' = space (12)
                  where 1 = 2
               union
                  select substring (access, 1, 16),
                         isnull (substring (objtext, 1, 47), '-'),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext in ('-', '(all)')
               union
                  select substring (access, 1, 16),
                         substring (objtext + ' (' + coltext + ')', 1, 47),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext not in ('-', '(all)')
               order by OBJECT, 'GROUP/USER', ACCESS
            end
         else
            begin
                  select ACCESS       = space (23),
                         OBJECT       = space (40),
                         'GROUP/USER' = space (12)
                  where 1 = 2
               union
                  select access,
                         isnull (substring (objtext, 1, 40), '-'),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext in ('-', '(all)')
               union
                  select access,
                         substring (objtext + ' (' + coltext + ')', 1, 40),
                         substring (user_name (uid), 1, 12)
                  from #grantsall
                  where coltext not in ('-', '(all)')
               order by OBJECT, 'GROUP/USER', 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

   if (exists (select *
               from sysprotects
               where action in (198, 203, 207, 222, 228, 233, 235, 236)))
      begin
         select ACCESS = substring (vTP.name + ' ' + vTA.name, 1, 24),
                'USER' = substring (isnull (user_name (p.uid), '?'), 1, 24),
                'FROM' = substring (isnull (user_name (p.grantor), '?'), 1, 24)
         from sybsystemprocs.dbo.usptvalues vTA,
              sybsystemprocs.dbo.usptvalues vTP,
              sysprotects p
         where p.action   in (198, 203, 207, 222, 228, 233, 235, 236)
           and vTP.type    = 'TP'
           and vTP.number  = p.protecttype
           and vTA.type    = 'TA'
           and vTA.number  = p.action
         order by ACCESS, user_name (p.uid)
      end
   else
      begin
         print '    THERE ARE NO "CREATE" PROTECTIONS.'

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

create procedure sp_grantsme (@GROUP_FLAG  varchar (1) = NULL,
                              @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_grantsme> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @user varchar (30)

   select @user = user_name ()

   exec sp_grantsuserdetail @user, @GROUP_FLAG, @COLUMN_FLAG
go
sp_addcomment sp_grantsme, 15, 'Lists the explicit protections for myself'
go
sp_addcomment sp_grantsme, 15, '   if GROUP_FLAG is null (default) include group protections'
go
sp_addcomment sp_grantsme, 15, '   if COLUMN_FLAG is null (default) do not list individual columns'
go
sp_procxmode sp_grantsme, anymode
go

create procedure sp_grantsuser (@USER        varchar (30),
                                @GROUP_FLAG  varchar (1)  = NULL,
                                @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_grantsuser> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   if (exists (select *
               from sysusers
               where name = @USER))
      begin
         exec sp_grantsuserdetail @USER, @GROUP_FLAG, @COLUMN_FLAG
      end
   else
      begin
         print '    USER/GROUP <%1!> DOES NOT EXIST.', @USER

         print ' '
      end
go
sp_addcomment sp_grantsuser, 15, 'Lists the explicit protections for a USER or GROUP'
go
sp_addcomment sp_grantsuser, 15, '   if GROUP_FLAG is null (default) include group protections'
go
sp_addcomment sp_grantsuser, 15, '   if COLUMN_FLAG is null (default) do not list individual columns'
go
sp_procxmode sp_grantsuser, anymode
go
