Enabling xp_cmdshell in SQL Server 2005 onwards

This procedure has been disabled from SQL Server 2005 onwards, when the ‘Surface Area Configuration’ tool was introduced as part of security enhancements within SQL Server.

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

To enable xp_cmdshell, run the following as a user with the sysadmin privelege:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

If you want to disable xp_cmdshell:

EXEC sp_configure 'xp_cmdshell', 0
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

You can also use the ‘Surface Area Configuration’ tool to do this, but I prefer using the method above.

Be aware that when you grant execute permission for xp_cmdshell to users, those users will be able to execute any operating-system command at the Windows NT command shell level, that the account running SQL Server (typically local system) has privileges to execute.

NOTE: Permissions will also need to be granted to the database user trying to invoke the xp_cmdshell procedure.