Related to: xp_regdeletekey
Both SQL Server 2000 and the upcoming SQL Server 2005 are COM-based Windows applications and, as such, make heavy use of the Windows registry. Even though Microsoft for years has discouraged saving application-specific data in the registry to avoid performance woes due to bloating and security problems, SQL Server has to interact with the registry. And because it has several internal stored procedures that can interact with the registry, your custom T-SQL code can too.
|Both SQL Server 2000 and the upcoming SQL Server 2005 are COM-based Windows applications and, as such, make heavy use of the Windows registry.|
This group of xp_reg procedures
provides many of the most useful features of the Win32 registry functions to read, write, delete, and enumerate registry keys and values. These are useful tools for an attacker, particularly since registry access permission through SQL Server is under the security context of the service process account, all too often Local System. That means that usually these procedures have unfettered access to pretty much every corner of the registry. So use these functions with caution and prevent their unauthorized use.
Here is a fairly benign use of xp_regread
to find out where the SQL Server help files are located.
DECLARE @helpPath varchar(450)
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL ...
@value = @helpPath OUT
On my fairly standard installation of SQL Server on my development machine, this code returns this path.
C:\Program Files\Microsoft SQL ...
Not overly useful information, but it shows just how easy it is to work with the registry. And with procedures like xp_regdeletekey
you can really do some damage. But you didn't get any such ideas here!
The stored procedure sp_fixindex
is a handy undocumented procedure that uses the also undocumented DBCC REPAIRINDEX
tool to fix corrupted indexes for system tables. It takes three parameters: the name of the database to repair, the table whose index you want to repair, and the index id of the problem index.
The code below repairs the indexes of the systypes
table in Northwind after forcing any dirty pages in memory to be written to disk with the CHECKPOINT
statement. Note that no other users can be connected to the database when you run this code, so you may need to set the Single User option to true using the sp_dboption
procedure in the master
sp_fixindex 'Northwind', 'systypes', 1
The undocumented status of this stored procedure, like some others I've covered here, is mentioned in at least one Microsoft KnowledgeBase article: 106122, "HOW TO: Use the sp_fixindex
Stored Procedure." Does that make it documented since Microsoft has publicly acknowledged its existence, even though it isn't in BOL? Only you can decide if this makes it worthy of use, since it still may not survive to the next version of the database.
Much More to Explore
Whew! That is a lot of system stored procedures to explore and make use of in your SQL Server and T-SQL development work. But there are many more that I considered including but didn't for various reasons mostly because I thought they were fairly well-known, were so esoteric to be of little use, or so convoluted and complex that they would need an entire article of their own.
You can learn a lot about SQL Server by exploring its built-in features. Microsoft kept most such features easily accessible and close to the surface for us to explore and use, even if they didn't always choose to document the feature.
Happy database coding!