Re: SQL Server Agent Proxy Account / XP_CMDSHELL
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 05/28/05
- Previous message: Jens Süßmeyer: "Re: login failed for user 'sa'"
- In reply to: John - PDX: "SQL Server Agent Proxy Account / XP_CMDSHELL"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 27 May 2005 18:53:18 -0500
> In reasearching this issue I should be able to set the sql server agent
> proxy account and then my user should be able to run the batch file that
> runs
> the osql that calls the sp that runs the xp_cmdshell - correct?
The proxy account is unrelated to SQL Server object permissions. A user
must have xp_cmdshell execute permissions when xp_cmdshell executed
directly. The proxy account is used as the OS security context when
xp_cmdshell is invoked by non-sysadmin role members.
The technique below shows how to implement an unbroken ownership chain to
xp_cmdshell so that you can provide xp_cmdshell functionality without direct
execute permissions on xp_cmdshell.
1) Create a dbo-owned user proc in an sa-owned database that executes
xp_cmdshell. Code the proc in such a way that only the intended command can
be executed and unintended code can't be injected.
2) Enable the 'db chaining' option (a.k.a. cross-database chaining) in your
user database. Note that this should be done when only sysadmin role
members can create dbo-owned objects in that database.
Also, there may be better ways to accomplish the task than xp_cmdshell. You
might provide details on what you are trying to do in case someone has a
better idea.
-- Hope this helps. Dan Guzman SQL Server MVP "John - PDX" <JohnPDX@discussions.microsoft.com> wrote in message news:41966260-1C3D-4208-B4B7-573CAA519E35@microsoft.com... > Thanks in advance. > > I am trying to run the xp_cmdshell and am calling it with osql from a > batch > file for a user. I have set the sql server agent proxy account with an > account that is both a domain admin & sql sysadmin (in our dev lab only) > and > still I get EXECUTE permission denied. > > I can make the user a sysadmin for test purposes only and all works fine. > I > can also go directly to the xp_cmdshell permissions, give the user execute > permissions and all works OK there as well. > > In reasearching this issue I should be able to set the sql server agent > proxy account and then my user should be able to run the batch file that > runs > the osql that calls the sp that runs the xp_cmdshell - correct? > -- > Thanks, > John
- Previous message: Jens Süßmeyer: "Re: login failed for user 'sa'"
- In reply to: John - PDX: "SQL Server Agent Proxy Account / XP_CMDSHELL"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
