Post reply

Calling A DTS Package from a Stored Procedure

  • 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

  • August 3, 2004 at 7:48 am

    #517443

    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.

  • August 3, 2004 at 9:57 pm

    #517539

    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

  • August 3, 2004 at 10:06 pm

    #517541

    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

     

  • August 4, 2004 at 2:52 am

    #517572

    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'

  • August 4, 2004 at 7:34 am

    #517625

    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

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

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