Post reply

how to execute xp_cmdshell command with non-sysadmin account

  • August 10, 2009 at 1:16 am

    #137917

    how to execute xp_cmdshell command with non-sysadmin account?

    kindly provide the steps for the same

    Sanket Ahir
    Don't run behind the success, Try to be eligible & success will run behind u......

  • August 10, 2009 at 2:01 am

    #1036620

    Users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell

    what type of process you would to do thro xp_cmdshell?

  • August 10, 2009 at 2:08 am

    #1036621

    i m having a script which contains xp_cmdshell which will execute ssis package stored on live server

    but user will execute that script through SSMS by connecting thr nonsysadmin account on his local PC(Remotely).

    Sanket Ahir
    Don't run behind the success, Try to be eligible & success will run behind u......

  • August 10, 2009 at 4:13 am

    #1036657

    You could add EXECUTE AS OWNER to the procedure that contains the xp_cmdshell call.

    This allows the user to execute that particular procedure with the rights of the user that owns the procedure, that means you.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • August 10, 2009 at 4:18 am

    #1036662

    can u specify the syntax for "execute as owner"?

    Sanket Ahir
    Don't run behind the success, Try to be eligible & success will run behind u......

  • August 10, 2009 at 4:20 am

    #1036663

    Take a look here.

    http://www.mssqltips.com/tip.asp?tip=1579

  • August 10, 2009 at 4:21 am

    #1036666

    and here

    http://blogs.msdn.com/sqlserverfaq/archive/2009/07/24/how-to-impersonate-the-privileges-to-create-a-login-using-the-stored-procedures-using-execute-as-clause.aspx

  • August 10, 2009 at 4:36 am

    #1036673

    but this is not applicable for xp_cmdshell

    Sanket Ahir
    Don't run behind the success, Try to be eligible & success will run behind u......

  • August 10, 2009 at 4:48 am

    #1036678

    It is never a good idea to allow a user to call xp_cmdshell. Instead look at the following.

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c571d5ed-8f31-4194-a875-b0b58d045e3a

  • August 11, 2009 at 5:45 am

    #1037333

    You need to create the xp_cmdshell proxy account.

    The FineBuild Reference Manual has step-by-step guidence on how to do this, and the security implications involved. You can get FineBuild from the link below or direct from CodePlex.

    I agree with some of the other posts that allowing non-sysadmin people to use xp_cmdshell can be a bad idea.

    Everything that non-sysadmin people do with xp_cmdshell uses the rights given to the proxy account. This means that if someone in Sales wants to run a script that uses xp_cmdshell you have to give the proxy account rights to the Sales files. Then if someone in Purchasing wants to run an xp_cmdshell script you have to give the proxy account rights to the Purchasing files. This would allow the Sales and Purchasing people to access each other's files via xp_cmdshell when otherwise company policy might prevent this from hppening.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • August 12, 2009 at 8:31 am

    #1038019

    Step 1: Find the most solid wall you can and bang your head against it repeatedly until you no longer think letting non-sysadmins run xp_cmdshell is a good idea.

    Step 2: Set up a SQL Agent job to run the SSIS package, or do whatever other sysadmin-type action you want to allow.

    Step 3: Define an Alert that responds to a given error number, and define the alert response to execute the Agent job.

    Step 4: Any user can now use RAISERROR with that error number to invoke the job. It will run under the SQL Agent account.

    This is an alternative to using a procedure with EXECUTE AS OWNER that calls xp_cmdshell or sp_start_job. It was the best way to do it in SQL 2000. The EXECUTE AS method is probably more elegant, but the alert method works well. I have used "EXECUTE AS 'account'" and found it only works if you grant Impersonate rights on the account to the user, and if the stored procedure is in a database marked as trustworthy. "EXECUTE AS OWNER" may not have these problems, I haven't used it.

  • August 12, 2009 at 9:40 am

    #1038090

    There is a big difference between using the xp_cmdshell proxy and SQL 2005 subsystem proxies.

    The xp_cmdshell proxy results in one set of rights for everyone who uses it. It means everyone who uses it has more rights than they need. 🙁

    SQL 2005 and 2008 SQL Agent subsystem proxies are completely different. Although you can set these up so one proxy is used by everyone, it is far better to set up one proxy for each non-sysadmin job owner.

    If you set up one proxy per job owner, then you only need to grant the rights needed by that job owner. You then end up with jobs being run without sysadmin rights (a good thing for security!), with each job proxy having only the rights needed for that account (also good for security!).

    If you are not sure how to set up subsystem proxies, look at BOL. If you are still confused, the FineBuild Reference manual has a step by step guide.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 12 posts - 1 through 11 (of 11 total)

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