How do i retrieve permissions os a Sybase object?

I am trying to get list of permissions on a stored procedure, say, in syabase.
I wanted to use SQL query t retrieve the permissions list.
Can I get some help on this please?
paeddyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan FranekCommented:
The information is stored in sysprotects system table.

I wrote a procedure to generate grant or revoke commands for given object some time ago. It works for ASE 12.5, I'm not 100% sure about ASE 15.
create procedure sp_ddl_obj_rights
(
    @obj_id int
)
as
  declare @obj_name varchar(30),
    @action tinyint,
    @user_name varchar(30),
    @protecttype tinyint,
    @action_name varchar(30),
    @text varchar(255) 
  declare cr cursor for
    select o.name,p.action,u.name,p.protecttype
      from dbo.sysobjects o,dbo.sysprotects p, dbo.sysusers u
        where o.id=@obj_id
          and o.id=p.id
          and p.uid=u.uid
    for read only 
  open cr
  fetch cr into @obj_name,@action,@user_name,@protecttype
  while @@sqlstatus=0
  begin
    select @text=case
        when @protecttype=0 then "GRANT %1! ON %2! TO %3! WITH GRANT OPTION"
        when @protecttype=1 then "GRANT %1! ON %2! TO %3!"
        when @protecttype=2 then "REVOKE %1! ON %2! FROM %3!"
      end,
      @action_name=case
          when @action=167 then "SET PROXY"
          when @action=193 then "SELECT"
          when @action=195 then "INSERT"
          when @action=196 then "DELETE"
          when @action=197 then "UPDATE"
          when @action=224 then "EXECUTE"
          when @action=151 then "REFERENCES"
          when @action=203 then "CREATE DATABASE"
          when @action=233 then "CREATE DEFAULT"
          when @action=222 then "CREATE PROCEDURE"
          when @action=236 then "CREATE RULE"
          when @action=198 then "CREATE TABLE"
          when @action=207 then "CREATE VIEW"
          when @action=228 then "DUMP DATABASE"
          when @action=235 then "DUMP TRANSACTION"
        end
    print @text,@action_name,@obj_name,@user_name
    print "GO"
    fetch cr into @obj_name,@action,@user_name,@protecttype
  end 
  close cr
  deallocate cursor cr 

go

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ROMA CHAUHANProject LeadCommented:
This query will give you all the details realted to stored procedure .
Select routine_name,* from information_schema.routines
ROMA CHAUHANProject LeadCommented:
THis is the query which will give you the name of procedure whose permission is set.....

select o.name, u.name, permission_name, state, state_desc from sys.database_permissions p
inner join sys.all_objects o on p.major_id = o.object_id
inner join sys.database_principals u on p.grantee_principal_id = u.principal_id
where o.type='p'
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

ROMA CHAUHANProject LeadCommented:
I have tried Jan_Franek 's solution. But i am not able to even run that stored procedure in my analyzer. It is giving me the errors. So how can that solution be accepted.

I have tried my query and searched a lot about it and giving me the correct result.
Jan FranekCommented:
Hi roma2208,

it seems like you are on MS SQL server. Author says, he's on Sybase ASE 12.5. My procedure works on that server. Your query doesn't - there are no tables named all_objects or database_principals on Sybase ASE server.

So I object too - all points should go to me :-)

Jan
paeddyAuthor Commented:
Thank you very much Franek
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.