Post reply

How to know which user is having Exec permissions on my procedure?

  • July 14, 2006 at 3:46 am

    #114680

    Hi All,

    If I have to know who are all having (users) execute permissions for my stored proceudre, I can see this in Enterprise Manager.  But from Query Analyzer how can I know? 

    Please send me queries which shows all authorised users for my procedure.

    Thanks in Advance,

    Mahesh

  • July 14, 2006 at 4:11 am

    #649188

    Mahesh

    Try this.  But beware: if you have procedures of the same name owned by different owners then this will need a bit of refinement.

    John

    select o.name, user_name(p.grantee) from syspermissions p

    join sysobjects o on p.id = o.id

    where o.xtype = 'P'

      and o.name = 'MyProc' -- Comment out to see all stored procedures

    order by o.name

  • July 14, 2006 at 4:40 am

    #649194

    Mahesh,

    Use the system proc, sp_helprotect:

    exec sp_helprotect 'dbo.MyProc'

    Karl
    source control for SQL Server

  • July 17, 2006 at 12:41 am

    #649459

    Thanks Karl.

    Mahesh

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply