January 3, 2013 at 9:07 pm
I currently have some code that I run embedded in scripts such as:
exec xp_cmdshell 'dtexec /DTS "\MSDB\My_Package" /SERVER MyServer /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V '
But I want to give some power users the ability to run the packages in a script, without xp-cmdshell. Any suggestions ?
January 3, 2013 at 10:03 pm
Is CmdExec agent job type an option?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 4:03 am
Have them run a job ? That might work, but I'd need a way to let them start that job, which I doubt they can do.
January 4, 2013 at 8:49 am
Upgrade to SQL Server 2012? catalog.start_execution (SSISDB Database) Let me guess, not a likely option.
You are right to deny access to xp_cmdshell, ideally disabled. SQLCLR is a recommended replacements for xp_cmdshell. You could achieve the abstraction to dtexec using a SQLCLR stored proc that only exposed the options for dtexec you wanted to allow callers to set.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 4, 2013 at 10:46 am
homebrew01 (1/4/2013)
Have them run a job ? That might work, but I'd need a way to let them start that job, which I doubt they can do.
One thing we put into place at one job was a queuing system. The user (via a simple app, insert statement, or even SSRS report) can input the job name (just let them know the names of jobs and what the job does). Then a regularly scheduled job comes along and executes any jobs inserted into the queue. The queue would be a table with limited permissions. We did it via SSRS and a stored procedure. Drop down list with human friendly job names that correlated to actual job names. Then a stored procedure did the mapping and insert into the table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply