Running DTS package via stored procedure using xp_cmdshell does not work for some users, MS SQL Server

I have a DTS package that imports a text file into a table.  I have a stored procedure that uses xp_cmdshell to run the DTS package using a dtsrun command line.  My user is the creator of the DTS package and can run the stored procedure successfully.  Other users can run  the stored procedure with no error but the import does not succeed.  These same users can succuessfully run the import by running the same  command line that is passed to xp_cmdshell from a command prompt on their local machine.  I am attaching the code from the stored procedure.
	DECLARE @CmdText varchar(500)
		DELETE FROM dbo.tblHPNRosterImport
		SET  @CmdText = 'DTSRun /S "(local)" /N "ImportStateRosterData" /G "{10F3789C-9F95-4C40-9F8A-C91ADB256313}" /W "0" /E' 
		EXEC master.dbo.xp_cmdshell  @CmdText

Open in new window

Who is Participating?
rickchildConnect With a Mentor Commented:
I see, yes you had to allow SQLAgentCmdExec shell actions in job steps.  OK well good that its working.

It should be fine to use existing account as the proxy, as it's going to have to be a sysadmin anyway for the xp_cmdshell.

I think the most important security consideration is that you ideally want to deny the users permissions to the xp_cmdshell directly as this can be very powerful.  Because those users can now exec any xp_cmdshell on the server.

You may be able to grant xp_cmdshell permission to the Stored Procedure (Or the owner of ther stored procedure), but I think that would be another question for someone else as I'm not sure on that side of it.
Been a while since I used SQL2000, but aren't you able to give permissions to a stored procedure in SQL2000, rather than just using the permissions of a user?
dwoliverAuthor Commented:
I am not aware of a way to give permissions to a stored procedure.  The users in question have permission to run the stored procedure that appears in my question and they have been given permission to run xp_cmdshell.   Since they can run the DTS package from their own command line, I don't think that the DTS package is the problem.  Also, the users in question can  successfully complete the import if they are in the sysadmin role, which is obviously not a solution.  Any suggestions about how to test this situation, get some error information while running in the users context.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

My first step would be to check that the users do indeed have permissions to run xp_cmdshell, particularly as making the users sysadmin fixes the issue.
Just make it run something simple, like copying a file, as a test, and I think it will probably fail if you read the below.  You probably need to set up the proxy account.

Is your server running Win98, or Windows XP/2000/Server?

By default, only the sysadmin can execute xp_cmdshell.
I would assume that the creator of the SP is probably a sysadmin.

- When xp_cmdshell is run by a sysadmine, xp_cmdshell will be executed under the security context in which the SQL Server service is running.

- When the user is not sysadmin (and server running Windows NT/2000/Server, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account.
If the proxy account is not set up, xp_cmdshell will fail.

On Windows 98, xp_cmdshell is executed as the user.

Anyway, please test a simple xp_cmdshell and we can rule this out one way or the other
dwoliverAuthor Commented:
I followed your suggestions and tested xp_cmdshell.  It did fail for non sysadmin users.  So I set the sqlagent_proxy_account to an account with sysadmin privileges.  But that was not sufficient to solve the problem.  I discovered that you have to enable Non-SysAdmin job step proxy account, which I did via Enterpirse Manager.  After that non sysadmin users are able to execute xp_cmdshell and my DTS package import can be executed by nonsysdmin users that have execute permission on xp_cmdshell.

Two followup questions.  From  a security point of view, is it best to have a distinct account to be the sqlagent_proxy_account, or is it OK to use an existing administrator account?  
dwoliverAuthor Commented:
Thanks for your guidance!
Would it make sense to put this process in a stored_proc and give execute permissions to those users for those procs, rather than giving them xp_cmdshell permissions?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.