June 17, 2013 at 10:08 am
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"';
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 17, 2013 at 10:19 am
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?
June 17, 2013 at 10:27 am
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?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 17, 2013 at 10:34 am
What about giving them permissions to run sp_start_job?
June 17, 2013 at 9:35 pm
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 ?
June 18, 2013 at 8:00 am
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 ?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply