Post reply

Using xp_cmdshell to grant fulladmin (read/write/xecute/full control) to a sql user

  • July 6, 2015 at 8:50 am

    #302207

    Hello Experts,

    Good Morning

    I want to grant a SQL USer Full Privilege to a folder in a path using xp_cmdshell as I dont have RDP rights.

    I dump the output of job to a folder in a location and due to lack to permisison the files are NOT being created.

    Kindly assist.

    Thanks

    Thanks.

  • July 6, 2015 at 9:25 am

    #1810484

    Have your sysadmins grant the permissions for you or let you RDP in the SQL Server box. Don't try to circumvent security with xp_cmdshell.

    BTW, if the SQL Server service account is not an administrator (and it shouldn't) it won't work.

    -- Gianluca Sartori

  • July 6, 2015 at 9:44 am

    #1810497

    The Xp_cmdshell account, which is the SQL service account or proxy, likely doesn't have rights to grant permissions.

    What you should do is have the account that runs the job be part of a group (domain or local) and grant the rights there. That way if the account changes, you can add the new account to the group and get all the rights, rather than having to track them down.

  • July 6, 2015 at 9:51 am

    #1810502

    Thnx Steve n Gian

    In one instance, I found no Spn registered to the sql account thru that sql runs.

    On few other servers, i found spn is regustered stil thereare permissio. Issue the job steps failed to create output files properly.

    The jobs r running with sa account by d way.

    Thanks.

  • July 6, 2015 at 12:10 pm

    #1810534

    when running as a sql server account the job will impersonate the service account at the OS level. If the service account has permission to the folder then it will be successful.

    Setup a credential and map to a file system proxy to do the necessary work for you.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 6, 2015 at 2:18 pm

    #1810573

    SQL-DBA-01 (7/6/2015)

    Hello Experts,

    Good Morning

    I want to grant a SQL USer Full Privilege to a folder in a path using xp_cmdshell as I dont have RDP rights.

    I dump the output of job to a folder in a location and due to lack to permisison the files are NOT being created.

    Kindly assist.

    Thanks

    My recommendation would be for you to get ahold of whomever is in charge of the box and get the privs using proper channels. If you did this where I worked, there would be a serious pork chop session in store for you for trying to backdoor a security that you have no business changing. You could get fired for this. Stop what you're doing and ask the right people. If they don't cooperate, there may be a reason but stop trying to do this now.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply