How to list Extended stored procedure for public

09 August,2017 by Jack Vamvas

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

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to list Extended stored procedure for public


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer