Why is xp_cmdshell disabled?
Updated: 6/20/2008 12:25:00 AM
Requests: 30026

As you are getting up to speed with SQL Server 2005, you might notice that scripts using master.dbo.xp_cmdshell no longer work:

Msg 15501, Level 16, State 1, Procedure xp_cmdshell, Line 1
This module has been marked OFF. Turn on 'xp_cmdshell' in order to be able to access the module.

This change is by design and is part of Microsoft's overall "secure by default" approach. However, the error message is not very helpful in solving the problem, as there is no magic switch in Management Studio to enable this feature. To turn xp_cmdshell functionality back on, use the sa account or another administrator, and issue this code:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

Seems like a lot of code. But if you try this by itself:

EXEC sp_configure 'xp_cmdshell', 1

You will get this error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 50
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

© 2004-2014 Aaron Bertrand, All Rights Reserved. SQL Server 2005, of course, belongs to Microsoft.