create procedure "Public".UserColumnPermission(in sUser char(128))
result(TableColumnName char(128))
begin
  select distinct string(creator,'.',tname,'.',colname) as TableColumnName
    from sys.syscolauth
    where(grantee=sUser or grantee=any(select group_name from sys.sysgroups
      where member_name=sUser)) and creator='Owner'
end

go
grant execute on "Public".UserColumnPermission to "Public"
go

create procedure "Public".UserTablePermission(in sUserName char(128))
result(creatorname char(128),tablename char(128),selectauth bit,insertauth bit,deleteauth bit,updateauth bit,updatecols bit)
begin
  select screator,
    stname,
    "max"(ConvertCharAuthToBit(selectauth)),
    "max"(ConvertCharAuthToBit(insertauth)),
    "max"(ConvertCharAuthToBit(deleteauth)),
    "max"(ConvertCharAuthToBit(updateauth)),
    min(ConvertCharAuthToBit(updatecols))
    from sys.systabauth where(screator<>'sys' and screator<>'dbo')
    and(grantee=sUserName or grantee=any(select group_name from sys.sysgroups
      where member_name=sUserName))
    group by screator,stname
end
go

grant execute on "Public".UserTablePermission to "Public"
go

create function "Public".ConvertCharAuthToBit(in cAuth char(1))
returns bit
begin
  declare bAuth bit;
  if cAuth='N' then
    set bAuth=0
  else
    set bAuth=1
  end if
  ;
  return(bAuth)
end

go

grant execute on "Public".ConvertCharAuthToBit to "Public"

go


create procedure Public.UserStorProcPermission(in sUserName char(128))
result(proc_name char(128))
begin
  select distinct string((select user_name from sys.sysuserperms as sysB
      where sysB.user_id=sysprocedure.creator),'.',proc_name) as procname
    from sys.sysprocedure join sys.sysprocperm on sysprocedure.proc_id=sysprocperm.proc_id join
    sys.sysuserperms on sysprocperm.grantee=sysuserperms.user_id
    where(user_name=sUserName or user_name=any(select group_name from sys.sysgroups
      where member_name=sUserName)) and not creator=any(select user_id from sys.sysuserperms where user_name='sys' or user_name='dbo')
end
go

grant execute on "Public".UserStorProcPermission to "Public"
go

grant connect to "Ben Snowing" identified by "snow"
go

grant select, update on "DBA"."customer" to "Ben Snowing"
go
