THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Xp_cmdshell and permissions

This blog has moved! You can find this content at the following new location:

Published Thursday, August 23, 2007 9:12 AM by TiborKaraszi



Berzat said:

Wery nice and clean text. I like the "rollback" at the end :-)

September 6, 2007 9:52 AM

TonyZeng said:

In SQL Server 2005 Cluster, it seems that have some problem.

I always got the error information like below:

Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1

An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'.

Note: In this TSQL:

EXEC sp_xp_cmdshell_proxy_account 'domain\tonyzeng','pwd'

domain\tonyzeng belongs to the administrators group of this computer.

September 17, 2007 10:15 PM

TiborKaraszi said:

Most probably the service account for the SQL Server service (not the proxy account) don't have appropriate Windows priviliges to create a process as some other user. I prefer to change service account using "SQL Server Configuration Manager". Also, the priviliges needed are listed in Books Online.

September 18, 2007 2:08 AM

TonyZeng said:

Thank you, TiborKaraszi.

But the service account for the SQL Server service also belongs to  the administrators group of the computer.It's not enough,  still need other priviliges?

By the way, could you tell me what are the priviliges needed in Books Online? Thanks for your kindly help.

September 18, 2007 7:16 PM

TiborKaraszi said:

No, Administrator is not enough. Again, do yourself a favour and set the service account using the "SQL Server Configuration Manager" tool, which will make sure that the account has apropriate permissions.

Here's a Books Online URL that lists the permissions needed:


September 24, 2007 4:14 AM

ravi said:


I have tried the steps outlined by you for the xp_cmdshell issue on two SQL 2005 servers. One one server i am able to use xp_cmdshell, while on the other server i keep getting the "A call to 'CreateProcessAsUser' failed with error code: '1314'.". Any advice?

Thank You


September 26, 2007 2:19 AM

TiborKaraszi said:

I would still suspect that the SQL Server service account is lacking some of the needed priviliges. If you are confindent that this isn't the case, I recommend you open a case with MS Support.

September 26, 2007 2:51 AM

Berzat said:

Try to add the SQL Server service account in this windows group: SQLServer2005MSSQLUser$ComputerName$InstanceName...

September 26, 2007 4:34 AM

shahgols said:

I found Berzat's comment to work for me.  

December 12, 2007 7:51 PM

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM

Matthew said:

if you change the permissions to give the service account the ability to use xp_cmdshell you may have to wait until your server refreshes these settings, this takes 90 mins. Or you can restart. I think you need to allow 'Replace a process level token'.

April 21, 2008 10:09 PM

Rushikesh said:

I tried all the options suggested in above post but nothing is working for me.

AS SQL sever 2005 is running on windows sever 2003 which is need not required to restart in case of service account change, but tried restart sever option also ,but its not working and giving the same error A call to 'CreateProcessAsUser' failed with error code: '1314'.Please help. Thanks



July 22, 2008 1:36 AM

TiborKaraszi said:

That error message suggest that the SQL Server service account lacks some of the required permissions. Was there created a number of groups when you installed your instances, for instance on my machine for a default instance I have a group named "SQLServer2005MSSQLUser$TIBWORK$MSSQLSERVER". See if you have a s imilar and verify that the SQL Server service account is a member of this group.

July 22, 2008 2:01 AM

Rushikesh said:

Thanks lot for quick reply.

I add Server service account to all these groups. But facing the same error do I need to restart the server?



July 22, 2008 2:33 AM

Glenn Koproske said:

I have to make this work on an application role name.  Our software project has the users log in and then the role takes effect.  The role apparently needs to execute xp_cmdshell, but I can't get the proxy account to accept the role name.

August 6, 2008 12:07 PM

Rowen Singh said:


I'm trying to launch a multiple instances of an external exe from sql server 2005. But I don't want sql to wait for the exe to finish before it continues to the next launch

Is there a command besides xp_cmdshell that can accomplish this ?

or do I have to resort to using the built int ole features?

Thanks for your help in advance

August 8, 2008 5:19 AM

TiborKaraszi said:

Perhaps consider Service Broker with external activation? (There has been some improvements with extenral activation in 2008.)

August 8, 2008 7:22 AM

satish said:

HI i can execute exe on the same machine where sql server 2005 is present

Ex:  exec xp_cmdshell 'c:\services\test.exe'

How can i  run the exe on sql server when sql server & exe are on two different machines

pls help me


October 21, 2008 4:24 PM

TiborKaraszi said:

You need some utility to from one machine start process on another machine. This is an OS issue, and I'm sure there are plenty such utilities available "out there". :-)

October 22, 2008 2:21 AM

Sray said:


I have followed these instructions and get to number 3 and then I get the following message:

Msg 15466, Level 16, State 2, Procedure sp_xp_cmdshell_proxy_account, Line 1

An error occurred during decryption.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

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.

What did I miss?

Thank you,


December 11, 2008 5:52 PM

Nancy said:

Thank you so much

I am using BCP in a recurring job and did not want to use an administrator account.

I successfully set this up in a 3 step job.

1.  Set permissions ((grant Execute & Proxy Account setup) (use master)

2.  The job on my database (use my db)

3.  Revoke the proxy settings & grant (use master)

May 15, 2009 11:42 AM

Altaf said:

Your solution worked in case when BUITIN\ADMINISTRATORS are enabled. As per our secrutiy policy this group is disabled, could you please tell me some workaround in this situation.

August 30, 2009 4:27 AM

Ninad said:

Hello there,

I tried the above mentioned steps but i m getting following error:-

Cannot execute as the server principal because the principal "SomeUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

below is the SP code snipit.

ALTER PROCEDURE [dbo].[eet_p_GetEquipmentImages]


@QuoteID nvarchar(55),

   @ServerName nvarchar(150),

   @ImageLocation nvarchar(150),

@ImageType nvarchar(15)




DECLARE @command as nvarchar(225)

DECLARE @URL as nvarchar(225)

DECLARE @EquipmentFolder nvarchar(150)

SELECT TOP 1 @EquipmentFolder=Product.ProductNumber FROM dbo.QuoteDetail --Product.Name

INNER JOIN Product ON  Product.ProductID = QuoteDetail.ProductID

WHERE quoteid = @QuoteID

SET @Command = 'DIR /B \\' + @ServerName + '\' + @ImageLocation + '\' + @EquipmentFolder + '\' + @ImageType

SET @URL = 'http://' + @ServerName + '/' + @ImageLocation + '/' + @EquipmentFolder + '/'

TRUNCATE TABLE eet_EquipmentImagesTempTable

EXECUTE AS login = 'SomeUser'

INSERT INTO eet_EquipmentImagesTempTable EXEC xp_CmdShell @Command


SELECT @URL + FileName as [FileName] FROM eet_EquipmentImagesTempTable WHERE FileName NOT LIKE '%The system cannot find the file specified.%'


SomeUser also has dbowner rights so that to insert data in eet_EquipmentImagesTempTable

This is wat i did to create SomeUser & to set the proxy user

USE master



GRANT EXECUTE ON xp_cmdshell TO SomeUser

EXEC sp_xp_cmdshell_proxy_account 'resource\user1','somepassword'

user1 is a domain user & a local admin on server & is also a sysadmin

m i missing anything here? Appreciated you help. Thanks.

February 24, 2010 7:27 PM

Ninad said:

CORRECTION:-- HERE I have used 'SomeUser' every where instead of (EETUser)

USE master



GRANT EXECUTE ON xp_cmdshell TO SomeUser

EXEC sp_xp_cmdshell_proxy_account 'resource\user1','somepassword'

February 24, 2010 7:30 PM

Jesse said:

I have a different issue.  I am running xp_cmdshell and do not have any permission issues.  It runs fine.  What I have found though is it is not running under the current user, it is instead running as the SQL Server account.  I need it to run under the user who is executing it.  The problem I have is I cannot use a EXECUTE AS statement becuase my users are added as Domain groups (like GROUP1).  So if I say ExECUTE as DOMAIN\USER it says cannot find becuase DOMAIN\USER is not a login (DOMAIN\GROUP1 is).  If I do a suser, i see USER not GROUP so I know SQL can pick it up.  The proc shows it is executed as USER but the output from xp_cmdshell shows it was executed as SQLSERVERUSER (the user who runs the sql service).  

November 30, 2010 9:59 AM

sage said:

Everything was ok til step 3! When i run step 3 it says something like this:

An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.

The account was ok but SQL stil reporting error... so I create crdental manualy with command:

create credential ##xp_cmdshell_proxy_account## with identity = 'SERVERNAME\useracount', secret = '42342eddds#'

(i m not on a domain, and servername mast be inculded because if you  not you will same error that windows user is not valid!!!)

next in Microsoft SQL Server Menagment studio  go to server Agent  an in proxies define new proxi

in General

proxy name: some name

 credetials: clisk on  ... button and select  


in Principals:

clisc add button and select User  click, OK..

and now run:

EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'

EXECUTE AS login = 'JohnDoe'

--Execution of xp_cmdshell is allowed.

--And executes successfully!!!

EXEC xp_cmdshell 'DIR C:\*.*'


and should be OK....

June 16, 2011 9:32 AM

Sachin said:

Hi Tibor,

I would like to thank you for your highly informative posts.

They are of immense help to a lot of us.

I have one (stupid) question.

If we create a proxy credential for a windows account as below:

EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'ds35efg##65";


Lets say, after a few months, for some reason the password changes for the account 'ADVWKS\Max04' what will be the impact?

I think that xp_cmdshell will stop execting for non sys admin users.

But any impact other than in will repeated calls to xp_cmdshell lock the account or something?

June 28, 2011 12:42 AM

TiborKaraszi said:


Glad it is helpful. Yes, xalls to xp_cmdshell by non-sysadmins will no longer work, and such calls can eventually cause account lock out. I suggest you use a dedicated account for this purpose because of this.

June 28, 2011 12:39 PM

Archana.N said:


  I need to load excel sheet with data from table in database. For this i used Xp_cmdshell to copy a template file for creating a new file used as the target ti be loaded. Also i used linked server and openquery commands to populate data into excel from table. The entire process i carried out in a server system. Now i need to do the same from remote system. so what all permissions would be needed to get the result same as executed in the server?

Kindly help at the earliest.

August 17, 2011 7:54 AM

test said:

How to check that xp_cmdshell permission is given to my dbuser before getting any error

October 19, 2011 7:58 AM

Manveen said:


I am facing an issue with xp_cmdshell.

I have followed all the steps:

EXEC sp_xp_cmdshell_proxy_account null

EXEC sp_configure 'xp_cmdshell', 0


EXEC sp_configure 'show advanced options', 1



EXEC sp_configure 'xp_cmdshell', 1



use master


GRANT EXECUTE ON xp_cmdshell TO dbuser;

EXEC sp_xp_cmdshell_proxy_account 'domain\id','pass';

EXECUTE AS login = 'dbuser'

EXEC xp_cmdshell 'DIR D:\*.*'



I am able to execute all the statements successfully except

the last one(for executing xp_cmdshell). The error is The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

What should i do??

July 31, 2012 6:34 AM

John said:


Thank you very much. Very straight forward and easy to follow. Have not had to setup a proxy before, so you turned a daunting task into a simple do-able solution. Thanks again!

August 20, 2012 12:08 PM

Alan said:

I am having the same exact error that Manveen is. Has anyone come up with a solution?

December 12, 2012 12:08 PM

Prakash said:

Hi Tibor,

It works fine for local system, but when i tried to access network drive it fails with access is denied. where as that proxy account is Administrator on that Network server.

EXEC xp_cmdshell 'DIR \\NetworkServerName\c$'

Can you please suggest ?

February 28, 2013 6:23 AM

Prakash said:

Hi Tibor,

I am sorry, above problem is resolved. Really my mistake.

Thank you very much for this post.

You saved my day.


February 28, 2013 6:27 AM

Floren said:

Amazing!!! Great article!!!


August 14, 2014 9:13 AM

Sreenivas said:

Hi All,

I am also facing a typical issue with XP_CMDSHELL .

while executing XP_CMDSHELL it is giving output null.

I had given all permissions to service account .

IF we restart the server then it is working 2 to 3 hrs then again same problem happening.

Can anyone help on this issue..

SQL2000 sp3

July 23, 2015 9:44 PM

Mg said:

Hi Tibor,

I have an sp that is running the xp_cmdshell.

I have a SQL Auth account for the app to use for the DB. Lets say it is AppSQL - minimarl privledges.  I have created a SQL Auth account with admin, lets say it is called InstSQLAdmin. I have altered the sp to use EXECUTE AS 'InstSQLAdmin.  xp_cmdshell is enable on the instance. I was expecting that when I connected to SSMS as appSQL, which has execute permission to the sp, that SQL would let me run xp_cmdshell as the admin account without needing a proxy. It failed.

So, is it safe to say that you can not get around this with an EXECUTE AS when using an admin account?

September 9, 2015 9:44 AM

TiborKaraszi said:

EXECUTE AS for the proc will "simulate" a user context. not a login context. So I'm not surprised that you have problems with this. I haven't played with this particular setup myself, but the behaviour you see does seem reasonable.

September 11, 2015 6:56 AM

MT said:

I am somewhat of a SQL rookie but used  the following to schedule a backup job in SQL to another server on the network.

EXEC xp_cmdshell 'net use L: \\BackupServer\D\ProductionServer /user:administrator Password'


I have to keep running the above because the backups fail to authenticate from time to time. The users of the system claim they are not changing the password and I explained to them that if they change the password (even if they change it to the same password) it will cause the backups to fail.

Is there another reason that would cause me to have to rerun the EXEC xp_cmdshell...?

September 30, 2015 5:54 PM

Vijay Ganji said:

Hi Sir,

I am facing issue with xp_cmdshell.

I have kept all information required for you in the below link:

Please help me.

I have to make xp_cmdshell as --xp_cmdshell to make the code work.I want to make it work without commenting the xp_cmdshell.

I have firewalls enabled,remote connections allowed,i am sysadmin ... but still no luck.please help me

July 9, 2016 12:16 AM

Muhammad Usman said:

I’ve done all that successfully but I am getting the following error

SQLState = 28000, NativeError = 18456

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘domain\account’.


what am I missing ?


March 15, 2017 11:42 AM
New Comments to this post are disabled
Privacy Statement