Alexander Chigrik is a regular contributor to www.sswug.org. He has always caught my attention by sniffing out un-documented features in each release of SQL Server, demonstrating how they may be of use. He has scored once again with his sswug editorial ,S”ome useful undocumented SQL Server 2016 extended stored procedures”. https://sswug.org/alexanderchigrik/sql-server/some-useful-undocumented-sql-server-2016-extended-stored-procedures/
There are a few reasons Microsoft does not publish these little gems. Some are due to security capabilities they expose, the potential to do harm un-intentionally, or simply because they don’t want to support them, they are intended for internal use.
I was looking at this latest list and wondering how you would use them in a business setting. The ability to make or retrieve registry settings was interesting. However, this is an area I’m not sure you want your database applications knowing about or reliant upon. What would you really use it for? I’m still attached to the notion that my database, and the server hosting it, should be dedicated to the persistence and retrieval of data as the highest priority. Anything beyond that may be better off hosted elsewhere. I wouldn’t put email and database services on the same server if I could afford to split it out.
So, what would you do with registry settings? If you are simply using the registry as a store for database operation configurations, would it not be better to handle that with your own native database objects in a user defined and managed database. I’m sure there are some things in the registry that impact how the database service behaves. Again, I would want to use a database call to manage those configuration settings. I’d prefer to use an external tool such as PowerShell for managing those kinds of configurations.
Managing files? Well that is something I can actually see using in some fashion. Managing backup files, etc. may be easier with extended procedures allowing to you to directly manage the file system. Still, I’m not sure that is really something you want your database service managing. Again, that’s what scripting languages are for, at the end of the day.
The other thing of concern is what the permissions need to be in order to use these extended procedures. If you are using the practice of the least permissions required for you services, you may have to elevate something in SQL Server in order to be able to use these extended or hidden stored procedures. We already have that issue with XP_CMDShell which allows a person to open a command sell from SQL Server and perform work as you would in a command window. By default, this feature is disabled in a SQL Server Service install. So, be security aware.
I’m not trying to downplay the hard work Alexander has done by bringing this rich library to us. My intention is to ask the harder question of should you use them? If you do use them, how are you going to secure their capabilities? What are you going to do when Microsoft drops them in the future?
Cheers,
Ben