Calling A DTS Package from a Stored Procedure

Question

Calling A DTS Package from a Stored Procedure

Craig Stonehouse

SSChasing Mays

Points: 602
More actions
August 2, 2004 at 11:15 pm

#109020

I have one of our desingers that has make a web site that call a stored procedure and then this SP call the DTS package. They are using the master..xp_cmdshell to run the dts. this works ok on their dev box but on the Production server the access writes are locked down. Now what I need to kwon is is there a way that there can run a dts package from a SP without giving the web user access to the Master DB. or is there a way that I can give the web user access only to see and run the xp_cmdshell.
Thanks
Craig

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

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

dhilditch SSC-Addicted Points: 495 More actions · Answer 1

Provided you don't use dynamic SQL inside your stored procedure you should be able to give access to the xp_cmdshell command just specifically to the stored procedure and provide execute permissions for that stored procedure to the web user.

Alternatively, you could modify the stored procedure to insert a row into a queing table - a table of DTSRun requests if you like - and then create a job to run every minute or so and check this table, and run the DTS task from the SQL job based on the contents of the table.

Either of those should work without having to give full access to the web user.

Dave Hilditch.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Bad news for you. Any login to SQL Server has access to the master database. The guest user is required to be activated on master. Therefore, if you have a login, you have access. You don't want to alter permissions on xp_cmdshell, especially for a SQL Server servicing a web site.

As for why a call from a stored procedure might work... that would be due to cross-database ownership chaining. So long as the stored procedure is owned by dbo and the database is owned by sa, but this is really an avenue you don't want to go down unless you have to do so.

From a security perspective, the best option is the queueing table as Dave has suggested. Is that an option?

K. Brian Kelley
@kbriankelley

Craig Stonehouse SSChasing Mays Points: 602 More actions · Answer 3

Thanks for the Information, We are going to use a Job that checks a table. like what Dave suggested.

Thanks onces more for your help.

Craig

corey lawson Hall of Fame Points: 3773 More actions · Answer 4

from http://www.devx.com/tips/Tip/17053

with more detail at: http://www.databasejournal.com/features/mssql/article.php/1459181

Running a DTS Package Through TSQL with a COM Concept

What if you wanted to use a COM object through TSQL Statement? SQL Server facilitates this task by provding a few system sorted procedures like sp_OACreate, sp_OAMethod, etc.

Check out the example below for how to execute a DTS package. It includes declaration, creation of an object, and uses the object methods "LoadfromStorageFile" and "Execute."

--Declaring variable

DECLARE @objdts int

--Creating object

EXEC sp_OACreate 'DTS.Package', @objdts OUTPUT

--Loading DTS storage file to object

EXEC sp_OAMethod @objdts, 'LoadFromStorageFile', NULL, 'C:\MyDTS.dts', ''

--Executing DTS package through an object

EXEC sp_OAMethod @objdts, 'Execute'

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

The sp_OA stored procedures also default to sysadmin role members only. These stored procedures can allow access to any objects (programming terminology) that are registered on that particular server. It's generally in the same security class as allowing xp_cmdshell.

K. Brian Kelley
@kbriankelley