November 19, 2009 at 5:54 am
Hi...I'm a member of a group of developers working with a fairly new company in the Healthcare arena, and we have always been members of the sysadmin group or assigned to a sysadmin role. Now...management is wanting to, and rightly so, begin restricting all users, including developers, to only those tasks that are required to perform their job function.
With that background in mind, the T-SQL developers have been using the xp_cmdshell to export the results of many stored procs to an FTP site to post 'txt', 'csv', 'hl7', etc. file types that our clients access based on their user settings. Now, with security restrictions, if we remove the developers from the sysadmin group or role, the use of xp_cmdshell is no longer an option...and after reading Brian Kelley's post of "Why We Recommend Against xp_Cmdshell" I understand the reasons why it is not such a good idea. Therefore, my plea. What other options are there to generate sp outputs to a network directory in the various formats listed above?
These sp's are either called directly from SS2K5 (SSMS) or from the execution of a specific SSIS package.
Any thoughts or ideas would be greatly appreciated. :w00t:
Thanks in advance,
Rick
Rick Mills, MT(ASCP)
Analyst / Programmer
Indiana Health Information Exchange
rmills@ihie.com
November 19, 2009 at 7:29 am
Well, you should be able to use SSIS to create the files. You would use the stored procedure(s) in the dataflow source and then the destination can be a flat file.
Another option is to use PowerShell which will work as a cmdexec job type in SQL Server Agent.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 19, 2009 at 7:32 am
You can also use SQLCMD to "pull" data from a scheduled task or on demand as a batch file to a flat file.
November 19, 2009 at 8:45 am
OK...those are definately some ideas that I hadn't thought of...I'll give them some thought. Obviously my net goal is to make this something that isn't a pain in the backside, but yet work!
Any other ideas would also be welcomed...Thanks! 😀
Rick Mills, MT(ASCP)
Analyst / Programmer
Indiana Health Information Exchange
rmills@ihie.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply