473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

What user is executing the SP to use xp_cmdshell

I have a simple Stored procedure that I'm using to call the command shell to
execut DTSRUN (which, in turn, calls a DTS package):

CREATE PROCEDURE updateDB AS
Exec master..xp_cmdshell
'DTSRUN my paramaters...'

I'm calling this procedure from an asp.net page. I'm getting the following
error:

An error has occured:System.Data.SqlClient.SqlException: EXECUTE permission
denied on object 'xp_cmdshell', database 'master', owner 'dbo'.

Obviously, it's a permissions issue. It looks like I need the DB admin to
set up a new user account that has permission to run the xp_cmdshell SP.
Does that sound about right? If so, how do I go about calling that procedure
from my procedure under that account name?

-Darrel
Nov 19 '05 #1
2 2356
you are correct, xp_cmdshell can only be run by a sqlserver sysadmin. your
stored proc can not change its login, the caller of the proc must connect to
sqlserver under an account that has been added to sqlserver sysadmin group.

you might look at setting up the dts package under sqlagent, and having the
proc scedule a run of the job.

-- bruce (sqlwork.com)

"darrel" <no*****@hotmail.com> wrote in message
news:uQ**************@TK2MSFTNGP12.phx.gbl...
| I have a simple Stored procedure that I'm using to call the command shell
to
| execut DTSRUN (which, in turn, calls a DTS package):
|
| CREATE PROCEDURE updateDB AS
| Exec master..xp_cmdshell
| 'DTSRUN my paramaters...'
|
| I'm calling this procedure from an asp.net page. I'm getting the following
| error:
|
| An error has occured:System.Data.SqlClient.SqlException: EXECUTE
permission
| denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
|
| Obviously, it's a permissions issue. It looks like I need the DB admin to
| set up a new user account that has permission to run the xp_cmdshell SP.
| Does that sound about right? If so, how do I go about calling that
procedure
| from my procedure under that account name?
|
| -Darrel
|
|
Nov 19 '05 #2
> you are correct, xp_cmdshell can only be run by a sqlserver sysadmin. your
stored proc can not change its login, the caller of the proc must connect to sqlserver under an account that has been added to sqlserver sysadmin group.
you might look at setting up the dts package under sqlagent, and having the proc scedule a run of the job.


Thanks, Bruce.

What I ended up doing was having our DB Admin set up a user that had
permission to run both the DTS package and the XP_cmdshell procedure.

We hit a snag with an error 997 GetProxyAccount, so it looks like my admin
will now have to set up a proxy account for this...

-Darrel
Nov 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Yvonne | last post by:
Would like to implement Blat on SQL Server 2000. I'm looking for example syntax for setting up BLAT with xp_cmdshell. TIA
1
by: Jagannathan Santhanam | last post by:
Hello I am trying to execute ‘xp_cmdshell' from within a DTS package that was created by another person. When I try to execute that ‘SQL Task' selectively from within the package, I get the...
3
by: Terri | last post by:
I'm using xp_cmdshell to output a text file from a trigger like this CREATE TRIGGER ON tblApplications FOR INSERT AS DECLARE @FirstName varchar(75) DECLARE @LastName varchar(75) Declare...
1
by: Steve Duke | last post by:
Here is the block of the sproc that I?ve created. All lines execute fine except for the ?exec master..xp_cmdshell @reboottc?. In order to make this work from the query analyzer I had to set the...
5
by: Narine | last post by:
Hi Folks, I am using an xp_cmdshell sp to start a C# app. This app downloads data files and imports them into DB. It has a logging functionality built-in and when executed from the command line...
2
by: Maddman | last post by:
Newbie here. In my database I'm needing to automate some data imports. I have the import set up as a DTS package and it works wonderfully. But I'm having trouble kicking it off as a stored...
7
by: Terri | last post by:
I have a stored procedure that creates an xml file. It executes a SELECT statement with the FOR XML clause and then writes the xml file using bcp and xp_cmdshell. I am calling this procedure by...
0
by: blueblueblue | last post by:
Hi, I am trying to execute my dts package from sql using command shell as exec master..xp_cmdshell 'dtsrun /Sfiutopiadb /Usa /P /NBulktest' The package works perfectly in enterprise...
1
by: ssshhh | last post by:
hi y'all! here i am again with another problem: declare @path varchar (100) declare @filename varchar (100) delcare @delete_this varchar (100) declare @cmd varchar (100) set @path =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.