I did get it working for some client. This article helped: http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm
Client had a stored proc that ran a couple DTS packages using xp_cmdshell. CF ran SP as a non-sysadmin SQL user, with execute permission on master.dbo.xp_cmdshell Here's the rule (changed in SQL 2000 SP3, and seemed like this SP broke when they did the update): Either you run xp_cmdshell as a user in the sysadmin role, in which case it runs the command as the SQL Agent service's login user (IIRC), or you have to setup the non-sysadmin job proxy account (which really was a [EMAIL PROTECTED] to get working :} ) Not sure if all these steps were necessary, but here's what we did (this was a standalone system, so no group policies applied): 1. SQL user to run needs execute permission on master.dbo.xp_cmdshell 2. Created windows user, added to computer's Administrators group (temporarily - rule out filesystem permission issues) 3. Local security policy - allow that user to Logon as Service; close, reopen the local security policy, make sure the effective policy is Allow 4. Also used the SQL EM to (temporarily) change the SQL Agent's login user to that windows user, then changed back 5. I also had to restart the SQL Agent service, and the SQL Server service. I think that's all, and not sure 4 was necessary, but it worked, and the non-sysadmin SQL user now can run xp_cmdshell, with the SQL Agent proxy user set up. - David On 7/26/06, Snake <[EMAIL PROTECTED]> wrote: > Didn't work for me, thus why I had to go to the lengths I did. > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 26 July 2006 10:34 > To: CF-Talk > Subject: RE: execute a DTS package via Stored Proc? > > No, anyone can run it if given the necessary permissions within a proxy > account. > > -----Original Message----- > From: Snake [mailto:[EMAIL PROTECTED] > Sent: 25 July 2006 14:32 > To: CF-Talk > Subject: RE: execute a DTS package via Stored Proc? > > Don't you have to be the SA in order to do exec master..xp_cmdshell > @execcommand > > I don't remember the reasons, but I couldn't do it the easy way when I > tried. > I need to do it as a speciifc user who only had access to his own database. > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 25 July 2006 14:03 > To: CF-Talk > Subject: RE: execute a DTS package via Stored Proc? > > How do you mean? This will work if you have the correct settings in there no > worries - granted it does not have any defensive stuff in their but if your > DTS is say, a simple import - it will be job done. > > -----Original Message----- > From: Snake [mailto:[EMAIL PROTECTED] > Sent: 25 July 2006 14:15 > To: CF-Talk > Subject: RE: execute a DTS package via Stored Proc? > > If only it were that easy. > > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 25 July 2006 13:40 > To: CF-Talk > Subject: RE: execute a DTS package via Stored Proc? > > Holy sh*^....seriously long winded when this will do.. > > DECLARE @execcommand varchar(100) > select @execcommand = '"dtsrun /Syourserver /Uusername /Ppassword > /Npackagename'' > exec master..xp_cmdshell @execcommand > > :-) > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247815 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
