How do i retrieve permissions os a Sybase object?

paeddy
paeddy used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Commented:
This query will give you all the details realted to stored procedure .
Select routine_name,* from information_schema.routines

Commented:
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'
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Commented:
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.
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

Author

Commented:
Thank you very much Franek

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial