Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

running package from TSQL Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 10:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
Hi
I have a package which will be placed at a shared location and multiple user will execute it with some parameters. As users are non technical, I'll just provide a TQL script where they will specify parameters and execute it.
Here is the script I have. The problem here is that I can't give xp_cmdshell permission to all users for security reasons. I have tried putting it in job and then giving permission to run job. I also tried putting it in proc and giving EXEC permission. BUt seems none will work unless I give permission on xp_cmdshell. Is there a work around to this?


Declare @cmd varchar(1000)

DECLARE @DBName varchar(50)='MyDB1'--Change DB Name here

DECLARE @TableToBeLoaded_1 varchar(100)='MyTable'--Specify 1st Table
DECLARE @PackagePath varchar(1000)='D:\temp\'

DECLARE @MinWeekID int=0

DECLARE @MaxWeekID int=0

SELECT @Cmd = 'DTexec /FILE "' + @PackagePath + 'Package1.dtsx" /MAXCONCURRENT 1 /REPORTING EW /DECRYPT abcd'
+' /SET \Package.Variables[User::DatabaseName].Value;"' + CAST(@DBName As varchar(50)) + '"'
+' /SET \Package.Variables[User::MinWeekID].Value;"' + CAST(@MinWeekID As varchar(5)) + '"'
+' /SET \Package.Variables[User::MaxWeekID].Value;"' + CAST(@MaxWeekID As varchar(5)) + '"';

print @cmd

EXEC xp_cmdshell 'DTexec /FILE "D:\temp\Package1.dtsx" /MAXCONCURRENT 1 /REPORTING EW /DECRYPT abcd /SET \Package.Variables[User::DatabaseName].Value;"ProjectDB3" /SET \Package.Variables[User::MinWeekID].Value;"0" /SET \Package.Variables[User::MaxWeekID].Value;"0"';


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1464243
Posted Monday, June 17, 2013 10:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:09 PM
Points: 901, Visits: 7,180
How close to real-time does this have to be? Maybe you can just have the users post a 'request' to a table and have a job that runs frequently looking for 'requests'. What would happen if multiple people tried to run the process at the same time?



And then again, I might be wrong ...
David Webb
Post #1464261
Posted Monday, June 17, 2013 10:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
it has to be real time as the package will pull some data for user and put in other tables.
Multiple user is no pbm as we have restricted users to pull same data with validations.
David Webb-CDS (6/17/2013)
How close to real-time does this have to be? Maybe you can just have the users post a 'request' to a table and have a job that runs frequently looking for 'requests'. What would happen if multiple people tried to run the process at the same time?


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1464269
Posted Monday, June 17, 2013 10:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:09 PM
Points: 901, Visits: 7,180
What about giving them permissions to run sp_start_job?



And then again, I might be wrong ...
David Webb
Post #1464272
Posted Monday, June 17, 2013 9:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 2,967, Visits: 2,569
Have you tried putting your code in a stored procedure and using the "EXECUTE AS" option in that stored procedure so that it executes as a user who is permitted to run xp_cmdshell ?


Post #1464448
Posted Tuesday, June 18, 2013 8:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
This worked...thanks. Didn't try the job one. But thanks for that as well..
happycat59 (6/17/2013)
Have you tried putting your code in a stored procedure and using the "EXECUTE AS" option in that stored procedure so that it executes as a user who is permitted to run xp_cmdshell ?


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1464669
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse