locked
Get a list of Extended Stored Procedures and permissions RRS feed

  • Question

  • I want to get a list of extended stored procedures and its grant permissions.  I'm having issues seeing the extended stored procedures using sys.objects.  I know you can only see what you have access to but I have sysadmin permission.  What am I doing wrong?

    --Tested on SQL 2014

    use master;
    SELECT * FROM sys.objects WHERE type = 'X'

    Thanks in advance
    • Edited by RKD_SG Wednesday, January 27, 2016 6:19 PM
    Wednesday, January 27, 2016 6:19 PM

Answers

  • SELECT * FROM sys.all_objects WHERE type = 'X'
    • Proposed as answer by Naomi N Wednesday, January 27, 2016 10:40 PM
    • Marked as answer by RKD_SG Wednesday, January 27, 2016 11:16 PM
    Wednesday, January 27, 2016 10:36 PM

All replies

  • Hi,

    execute your statement against the database that you are interested in. In your query the command is being executed against master database.

    USE <database_name>
    GO
    SELECT * FROM sys.objects WHERE type = 'X' 
     

    Hope this helps

    Thanks

    Bhanu

    • Marked as answer by RKD_SG Wednesday, January 27, 2016 10:32 PM
    • Unmarked as answer by RKD_SG Wednesday, January 27, 2016 11:15 PM
    Wednesday, January 27, 2016 6:28 PM
  • The master database has the XP's I'm looking for.  It's strange that I can't query the list.

    


    • Edited by RKD_SG Wednesday, January 27, 2016 6:46 PM
    Wednesday, January 27, 2016 6:36 PM
  • I got an empty list with the first query and lots of procedures using the second. The documentation says it's going to be removed but doesn't tell much how to replace that functionality:

    use master;
    SELECT * FROM sys.objects WHERE type = 'X'
    
    execute sp_helpextendedproc


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by RKD_SG Wednesday, January 27, 2016 10:32 PM
    • Unmarked as answer by RKD_SG Wednesday, January 27, 2016 11:15 PM
    Wednesday, January 27, 2016 6:52 PM
  • SELECT OBJECT_ID(object) still works so I think I got what I need.  It's still strange that sys.objects won't display them.  Thanks guys for your help!
    Wednesday, January 27, 2016 10:33 PM
  • SELECT * FROM sys.all_objects WHERE type = 'X'
    • Proposed as answer by Naomi N Wednesday, January 27, 2016 10:40 PM
    • Marked as answer by RKD_SG Wednesday, January 27, 2016 11:16 PM
    Wednesday, January 27, 2016 10:36 PM