XP_CMDSHELL is one of those features that is turned off by default on SQL Server and needs to be turned on to function.
If xp_cmdshell has not been turned on you will get this error when you try to use it.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 17]
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 fix this you need to turn on xp_cmdshell, which can be done with the following sql script.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
If you are interested in the security implications, take a look at this earlier blog post with details. Is enabling xp_cmdshell a security risk?
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!