Enabling xp_cmdshell in SQL Server

By:   |   Updated: 2021-10-13   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Security


Sometimes there may be a need to run some external processing from within Microsoft SQL Server. So, to make this task easy for both Database Administrators (DBAs) and Developers, Microsoft has a built-in extended stored procedure, called xp_cmdshell. With this extended stored procedure you have the ability to run any command line process, so you can embed this within your stored procedures, jobs or batch processing. This option is disabled by default. Also, to limit access to using xp_cmdshell only members of the sysadmin server role have default rights.


In this tutorial we will look at how to enable xp_cmshell and some of the errors you may see.

SQL Server Errors for xp_cmdshell

Here are some errors you may encounter with xp_cmdshell.

SQL Server blocked access to procedure sys.xp_cmdshell

If you have sysadmin privileges and don't enable xp_cmdshell and you issue a T-SQL command such as the following to get a directory listing of the C: drive in SQL Server Management Studio (SSMS):

xp_cmdshell 'dir c:\'

you get the following error message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
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', search for 'xp_cmdshell' in SQL Server Books Online.

Execute Permission Denied on xp_cmdshell

If you don't have sysadmin privileges and try to run xp_cmdshell whether it is enabled or not you get this error message:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

The configuration option xp_cmdshell does not exist

Another error you may get if you try to enable xp_cmdshell using sp_configure when advanced options is not set is the following error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 2]
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

So in order to use xp_cmdshell whether you are a sysadmin or a regular user you need to first enable the use of xp_cmdshell.

Enable xp_cmdshell with sp_configure

The following code with enable xp_cmdshell using sp_configure. You need to issue the RECONFIGURE command after each of these settings for it to take effect.

-- this turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '1'
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1' 

Disable xp_cmdshell with sp_configure

The following code with disable xp_cmdshell using sp_configure:

-- this turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '1'
-- this disables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '0' 

Enable or Disable xp_cmdshell with SSMS Facets

From within SSMS, right click on the instance name and select Facets.

sql server facets

In the Facet dropdown, change to Server Security as shown below.

You can then change the setting for XpCmdShellEnabled as needed to either True or False. After changing the value, click OK to save the setting and the change will take effect immediately. There is not a need to enable show advanced options or use reconfigure, the GUI takes care of this automatically.

sql server facets

Granting Access to xp_cmdshell

Let's say we have a user that is not a sysadmin, but is a user of the master database and we want to grant access to run xp_cmdshell.

-- add user test to the master database
USE master

-- grant execute access to xp_cmdshell
GRANT EXEC ON xp_cmdshell TO [test]

We get this error message:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

There is not a need to give a user sysadmin permissions or elevated permissions to run xp_cmdshell. To do so you can create a proxy account as shown in this tip Creating a SQL Server proxy account to run xp_cmdshell.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-10-13

Comments For This Article

Friday, October 7, 2022 - 11:22:22 AM - Greg Robidoux Back To Top (90569)
Hi Jim,

Not sure what your VBS script does, but it looks like there might be a permissions issue where you are trying to create a file.

When you use xp_cmdshell it uses the credentials of the SQL Server database engine account.


Friday, October 7, 2022 - 8:58:23 AM - jim Back To Top (90568)
hi Greg,
when I'm trying to execute via ssms xp_cmdshell 'cscript c:\temp\Unlock_INPUT_File.vbs' I'm getting this error
Microsoft VBScript runtime error: Permission denied: 'CreateObject'
in this line
Set objExcel = CreateObject("Excel.Application")

any help would be appreciated

Thursday, March 10, 2022 - 9:21:48 AM - Oliver Back To Top (89867)
Did not know about changing this through facets, good to know !

Tuesday, July 7, 2020 - 9:40:38 AM - Jeff Moden Back To Top (86096)

I realize this is an older article but my comment is that you should NEVER grant access to a non-DBA user to execute xp_CmdShell.  Whatever needs to be done by a non-DBA should be done in a well written stored procedure and then give the user access to execute that stored procedure.  And, yes, there's a pretty simple way to pull this off so that things remain safe but I don't want to write an article in a post for how to do it. :D

Thursday, July 21, 2016 - 4:43:13 AM - Nandakumar Back To Top (41934)

I do not know how to  enable the xp_cmdshell in SQL Server 2005 . and then i did see your website and  i enabled it in my client database.

thanks a lot . No words to say .



Monday, September 30, 2013 - 4:19:47 PM - Adelson Back To Top (26996)

Thanks !!!!

Tuesday, September 24, 2013 - 4:26:47 AM - stroct1 Back To Top (26909)

Thanks for helping

Wednesday, November 14, 2012 - 3:37:37 AM - narasimha Back To Top (20332)

thanks a lot , it is very helpful to us keep it uptopdate more and more 


best regards,


get free sql tips
agree to terms