Risky Stored Procedures

Stored procedures are code functions stored in DLL files that let SQL Server execute functions that aren't usually possible with SQL Server alone. For example, users can use stored procedures to add user accounts, modify Registry keys, and modify service properties.

Although stored procedures are powerful extensions to SQL Server, they can pose a significant risk. Therefore, consider removing many of them from the server. And be aware that by installing third-party applications, you or someone else might have added other stored procedures to your SQL Server in addition to SQL Server's built-in stored procedures. You need to review any third-party stored procedures so that you'll know what security implications they present.

Also, be advised that you can use the xp_cmdshell extended stored procedure to run almost any command on the system. Commands processed with this procedure execute as if you had entered them on a DOS command line, and the commands run under the security context of the SQL Server machine. So, for example, if your SQL Server machine runs under the context of NT's built-in SYSTEM account, any commands executed with xp_cmdshell are all-powerful. They can perform any action specified.

You can use the Registry-related stored procedures to manipulate the Windows NT Registry, which implies that someone can easily use these stored procedures to subvert system security. Remove these stored procedures, which Table A lists, unless you're 100 percent certain that you need them to properly operate your server. You can use OLE automation stored procedures, which Table B lists, to modify security properties of Windows NT-based services, such as IIS, among other tasks.

Table C contains a list of other stored procedures that can compromise security. Keep in mind that removing any stored procedure might break the functionality of SQL Server-based applications or SQL Server management tools. Be sure to test the stored procedure removal on a nonproduction server. I recommend that you run each application and management tool to ensure that you lose no required functionality. Before removing a procedure, note its definition so you can re-add the procedure in the event that its removal breaks overall functionality.