What user is executing the SP to use xp_cmdshell

[darrel]

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

 

[bruce barker]

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)



| 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
|
|

 

[darrel]

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