What user is executing the SP to use xp_cmdshell

Discussion in 'ASP .Net' started by darrel, Mar 2, 2005.

  1. darrel

    darrel Guest

    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, Mar 2, 2005
    #1
    1. Advertising

  2. darrel

    bruce barker Guest

    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" <> wrote in message
    news:...
    | 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, Mar 2, 2005
    #2
    1. Advertising

  3. darrel

    darrel Guest

    > 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
    darrel, Mar 2, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. m3ckon

    asp.net xp_cmdshell

    m3ckon, Sep 30, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    604
    Scott Allen
    Sep 30, 2004
  2. Lars Netzel
    Replies:
    0
    Views:
    945
    Lars Netzel
    Mar 4, 2005
  3. Kris G
    Replies:
    0
    Views:
    342
    Kris G
    Jun 23, 2006
  4. Amit Jain
    Replies:
    2
    Views:
    487
  5. Me
    Replies:
    3
    Views:
    397
    Aaron [SQL Server MVP]
    Nov 30, 2004
Loading...

Share This Page