Aside from those integrated into SQL Server (e.g. xp_cmdshell) it's not often I come across the need to use Extended Stored Procedures but an application I installed recently required that I add their custom procedure to the SQL Server housing the application data. Having been years since I had last added one it took a little figuring out since I would've expected to find it in Management Studio under "Server Objects" or "Management" but it's somewhere entirely different.
To add/register an Extended Stored Procedure you'll need to right-click on…
Databases >> System Databases >> master >> Programmability >> Extended Stored Procedures
…and select "New Extended Stored Procedure".
Next you'll have to provide a (this will be the name the procedure is called by) and the path to the DLL, you can browse to the .dll file by clicking on the ellipsis.
The next step is vital since at the moment the only people able to actually use the Extended Stored Procedure would by sysadmins. All you have to do to remedy that is click on "Permissions", click "Search", type "public" in the text box and click OK as follows…
All you have to do now is tick the "Execute" permission to allow the 'public' users of the database (effectively everybody) to be able to use the procedure.
The completed dialog box should look something like this…
Then click "OK" and all should be well.
