SQL Server Performance

xp_cmdshell

Discussion in 'General Developer Questions' started by Reddy, Aug 28, 2006.

  1. Reddy New Member

    I am using xp_cmd shell in my SP and am executing it through my App, but getting permissions denied error for that cmd, how can I resolve it.
    When I added my AppUser to xp_cmdshell its giving a diffrent error saying something abt proxy settings.
    Pls help.

    Thanks!
    "He laughs best who laughs last"

  2. satya Moderator

    Is that user is a part of SYSADMIN group?

    Make sure the XP_CMDSHELL is locked down for security purpose, if you want to execute the SP using a job then make sure the SQLAgent has required SA privileges. Also when executing from User databsae context try using master..xp_cmdshell.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. Reddy New Member

    its not a sysadmin grp.

    Thanks!
    "He laughs best who laughs last"

  4. satya Moderator

    http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm fyi on such security information.

    quote:
    If the table is owned by 'dbo', you can allow the trigger to use xp_cmdshell
    without direct user execute permissions by changing your user database owner
    to 'sa' (using sp_changedbowner). With SQL 2000 SP3, you'll also need to
    enable the 'db chaining' (a.k.a. cross-database chaining) option un your
    user database. This will provide an unbroken ownership chain between your
    trigger and the xp_cmdshell extended stored procedure.

    Note that you should enable 'db chaining' in an sa-owned database when only
    sysadmin role members have permissions to create dbo-owned objects in that
    database.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. Reddy New Member

    I have a AppUser executing that command, so for that how can I setup or grant permission to execute the cmd, pls help.

    Thanks!
    "He laughs best who laughs last"

  6. satya Moderator

    Grant EXECUTE permission on xp_cmdshell

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  7. Reddy New Member

    Thats fine, but how to setup proxy settings?
    where to know abt domain,user...

    Thanks!
    "He laughs best who laughs last"

  8. satya Moderator

    REfer to the books online.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page