SQL with Manoj

I was trying to execute a Windows Shell command from SSMS by using xp_cmdshell Extended Stored Procedure and encountered an error highlighted below in RED color:

EXEC xp_cmdshell 'DIR *.exe'

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
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.

Even if you are a sysadmin and as by default this option is turned off after a new SQL Server installation, you need to manually turn on this option, let’s see how:

-- To change the advanced options:
EXEC sp_configure 'show advanced options', 1
GO
-- To update the current value for advanced options:
RECONFIGURE
GO
-- To enable the xp_cmdshell option:
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the new configured value for xp_cmdshell option:
RECONFIGURE
GO

–> If you want to suppress the messages returned by the xp_cmdshell extended Stored Procedure use the optional parameter no_output.

exec xp_cmdshell 'DIR *.exe', no_output

Please Note: If xp_cmdshell is executed within a BATCH and returns an error, the complete batch will fail. In earlier versions of SQL Server the batch used to continue executing.

If the user is not member of sysadmin role and want to use this extended SP then a Proxy account credential need to be created by using sp_xp_cmdshell_proxy_account. Check this KB article for the same: link.