How to list Extended stored procedure for public

Extended stored procedures are used by developers to create routines. One of the main benefits of extended stored procedures is the ability to make  them appear as regular stored procedures.

i.e input parameters can be sent by the calling application , and result sets are returned.

SQL Server is installed with a bunch of extended stored procedure with permissions granted to public. You’ll recognise quite a few including : sp_cursor, sp_cursor_open ,sp_executesql . There are about 80 of them installed  with public permissions.

Microsoft announced to extended stored procedures will be deleted in future versions , so if you’re a developer think of using CLR integration . Also, you’ll need to start planning of converting the extended stored procedure to CLR Integration method.

This query lists extended stored procedures with permissions granted to public

SELECT     SO.name AS [Extended Stored Procedure]
             , USER_NAME(SPO.grantee) AS [Grantee]

    FROM    master.dbo.sysobjects AS SO
            INNER JOIN master.dbo.syspermissions AS SPO
            ON SO.id =SPO.id

    WHERE   (SO.type = 'X')
    ORDER BY SO.name

Related articles

Find Table Scans in Query Plan Cache

Memory grants and execution plans