SQL Server Performance Forum – Threads Archive
DTS not running unless user is sa
Hi,<br />I am writing a asp.net app where a user can select a parameter, send it to a stored procedure which runs a dts package based on the parameter. The web user has a different user (web_user) with db_datareader, db_datawriter and public access. web_user has permission to run the stored procedure. Here is the flow:<br /><br />user selects value from drop down list on web page and clicks submit —> <br />this is sent to a stored procedure via sqlparameter —> <br />stored procedure runs dts based on the value user selected (this does not work)<br /><br />Here is the stored procedure:<br /><br />CREATE PROCEDURE [web_IMPORT_DATA]<br />@RegionID VARCHAR(10)<br />AS<br /><br />/*<br /> Since I am calling from a db other then master I have to name db<br /> and owner.<br />*/<br /><br />IF @RegionID = ‘MW'<br />EXEC master.dbo.xp_cmdshell ‘dtsrun /S JUPITER /N Import_MW /E ‘<br />IF @RegionID = ‘NE'<br />EXEC master.dbo.xp_cmdshell ‘dtsrun /S JUPITER /N Import_NE /E ‘<br />IF @RegionID = ‘SO'<br />EXEC master.dbo.xp_cmdshell ‘dtsrun /S JUPITER /N Import_SO /E ‘<br /><br />SELECT ‘DONE! ‘ + @RegionID<br />GO<br /><br />JUPITER is the sql server and web_user has permission to execute xp_cmdshell. Also, I logged into sqlquery as web_user and <<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />assword> and did the following:<br /><br />EXEC web_IMPORT_DATA ‘NE'<br /><br />All I get is DONE! NE.<br />But if I run it after logging into sqlquery as sa and run the same thing then I get the data imported. Any help will be appriciated.<br /><br />Check the account privileges used to start MSSQLServer and MSSQLAgent services.
Ensure the specified web user has required privilege to execute the DTS package and involved objects (tables/SPs). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Originally posted by satya
Check the account privileges used to start MSSQLServer and MSSQLAgent services.
Ensure the specified web user has required privilege to execute the DTS package and involved objects (tables/SPs). SqlServer and Agent are started with windows domain administrator account. My authentication is: Sql Server and Windows (sql 2000). How would I go about setting permission on the package for that user. Nothing in the properties section of the package. Thank you for your help
Ensure the specified web user has required privilege to execute the DTS package and involved objects (tables/SPs). SqlServer and Agent are started with windows domain administrator account. My authentication is: Sql Server and Windows (sql 2000). How would I go about setting permission on the package for that user. Nothing in the properties section of the package. Thank you for your help
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;282463 to manage permission on a DTS package to deal the issue. http://www.databasejournal.com/features/mssql/article.php/3086891 – DTS information. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>