SQL: xp_cmdshell

[darrel]

anyone use this procedure? I'm calling it from another stored procedure, but
am getting permission issues. I don't have permission to execute it.

Here's my SP:

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

I'm guessing I need to set up a user with permission to access the
xp_cmdshell? Once I do that, how do I call it as that user? Can I do that
from my SP?

-Darrel

[Julian Roberts]

You'd probably need to be a system administrator to do this kinda stuff.

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[Chris In Madison]

Hmmm... Anything that runs out of the Master table requires pretty elevated
privileges. You'd almost have to execute this as SA, or with a user that
has admin privileges.

Best regards,
Chris

[Tom Muck]

> I'm guessing I need to set up a user with permission to access the
> xp_cmdshell? Once I do that, how do I call it as that user? Can I do that
> from my SP?

Whichever user calls the SP has to have permission to execute the
xp_cmdshell stored procedure. If you have admin rights in Master database,
you can add permission:

GRANT execute ON master.dbo.xp_cmdshell TO yourusername

However, the xp_cmdshell stored procedure is quite dangerous.

--
-------------------------------------------
Tom Muck
co-author Dreamweaver MX 2004: The Complete Reference
[url]http://www.tom-muck.com/[/url]

Extending Knowledge, Daily
[url]http://www.CommunityMX.com/[/url]

[darrel]

> You'd probably need to be a system administrator to do this kinda stuff.

Right...but I'm not sure what I need to do.

-Darrel

[darrel]

> Hmmm... Anything that runs out of the Master table requires pretty
elevated

> privileges. You'd almost have to execute this as SA, or with a user that
> has admin privileges.

Right...I guess that's the question. So, we need to make a new user with
admin privliges that is allowed to execute the XP. How do I then call that
XP as that user?

-Darrel

[darrel]

> Whichever user calls the SP has to have permission to execute the

> xp_cmdshell stored procedure. If you have admin rights in Master database,
> you can add permission:

Ah! OK, so that makes sense. I need to call my SP as the same user that has
permission to run the XP.

> However, the xp_cmdshell stored procedure is quite dangerous.

How dangerous? What I am doing is this:

We have a staging server and a public server. One database is updated
nightly via a DTS, but there have been requests to update it manually at
times.

I want to put a button on an ASPX page on our intranet, that, when clicked,
will call my SP which, in turn, calls the XP to execute the DTSRUN command
to manually run the DTS.

Would that cause problems?

-Darrel

[Tom Muck]

> I want to put a button on an ASPX page on our intranet, that, when
clicked,

> will call my SP which, in turn, calls the XP to execute the DTSRUN command
> to manually run the DTS.
>
> Would that cause problems?

Theoretically, yes, but practically, no. If this is being executed by a user
that you have set up for your web application, anything that the user has
access to is open to a hacker. If you use a different SQL user for your
admin pages, you are making the web application a little more secure. Having
said that, I use it myself for executing DTS from my admin pages.

Tom

[darrel]

> Theoretically, yes, but practically, no. If this is being executed by a
user

> that you have set up for your web application, anything that the user has
> access to is open to a hacker.

Oh. Gotcha. Well, this is all on our internal network, so I'm not going to
be too worried about it.

Good to know though!

-Darrel