February 24, 2006 at 6:36 am
Hello, I am running a Sql 2000 Enterprise Edition with sp4 server. I have a developer who created a stored procedure that runs xp_cmdshell to kick off a DTS package. When it was created, the developer had SA rights on the development server, so permission issues were not encountered.
The problem I am having is getting the stored procedure to run after dropping user security rights to DBO once the stored proc and DTS package was installed into the production server.
On the production sql server I have unchecked the box in the Sql Agent properties so that non-SA users can run xp_cmdshell; I have set the sql proxy agent to use the same account that Sql Server is running as; and that same account is an Administrator on the box.
Nothing is working with any rights lower than SA.
TH
February 27, 2006 at 8:00 am
This was removed by the editor as SPAM
February 28, 2006 at 6:43 pm
What error message/level is being returned to the conection that is issuing the xp_cmdshell? A better way is to have a stored procedure create and execute the package as a job... this would also give you control to insure that the job is not executed more than once.
If this looks like it could be a solution for you, search through the archives... I believe I posted code on how to do this about a year ago.
-Mike Gercevich
March 1, 2006 at 5:08 am
Thanks Mike.
That sounds like a good solution...
To answer your question, we're getting an error 1314 when a non-sa user runs this stored proc with the xp_cmdshell statement..
TH
March 5, 2006 at 1:22 am
You didn't mention the rights of the user who executes the sp that has cmdShell statement to run DTS package. If the Job is executed by the system, then it follows the login that SQL Agent service is running under which is an administrator...so no issues. But if the cmdShell is being executed seperately by a stored proc which is invoked by User1, then the cmdShell runs under the security context of User1, so it is required to have User1 access to file system. Also, xp_CmdShell stored proc should have access to get executed in User1 security context.
Try following
>> Set exec permissions to xp_CmdShell
>> Add User1 as a user to the path which is executed from CmdShell
March 5, 2006 at 1:37 am
Also forgot to mention, it checks the proxy account of sql agent for non sa users, so check the nt login used as a proxy account if it has permissions to exec the xp_cmdshell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply