How to send SP results to CSV file in DTS?

Question

How to send SP results to CSV file in DTS?

DBADave

SSChampion

Points: 10962
More actions
January 9, 2004 at 9:54 am

#85216

How do I setup DTS to invoke a stored procedure and have the results written to a CSV file?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

A transform data task will do this in SQL 2000. SQL 7 has issues. choose SQL Query and enter the stored procedure call on the source tab

DBADave SSChampion Points: 10962 More actions · Answer 2

Thanks Steve. We'll look into it.

On a somewhat related note, do you know how to restrict permissions with DTS so only specific people have the ability to create packages? I was told today any SQL Server user has the ability to create packages. If that is true, a security issue exists with the Send Mail task. Send Mail appears to work the same as xp_sendmail, which means it runs under SQL Server's security, which is usually local administrator. That being the case a SQL Server user can create a DTS package which emails an attachment to himself or someone else, where the attachment is a file or directory located on the database server. In other words, it's possible for someone to access information they should not be viewing. It probably wouldn't take much effort to locate a backup of master and send that as an attachment. You could then restore master to your own server and try to run various utilities to crack the passwords.

Thanks, Dave

Shas3 Hall of Fame Points: 3671 More actions · Answer 3

By default public member can view/create/delete the DTS packages unless you deny the access to the procedures sp_enum_dtspackages, sp_add_dtspackage in the msdb Database. Only DBOs and sysadmins can execute xp_sendmail

Shas3

DBADave SSChampion Points: 10962 More actions · Answer 4

We'll probably deny access to these procedures. I find it strange that xp_sendmail can only be executed by DBOs and sysadmins, yet DTS packages can be created by anyone. Why did they bother restricting xp_sendmail if they weren't going to restrict who can run Send Mail via DTS. It seams like a contradiction in security philosophies.

Thanks for the help,

Dave

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Definitely a loophole. Here we remove public and create a group in msdb that allows package creation/editing. A separate group is used for job executions on the very rare chance we grant it.

DBADave SSChampion Points: 10962 More actions · Answer 6

I like that approach. I'll see if the rest of our DBA team would like to pursue this option. Is there anything I need to be made aware of such as will some things stop working when public has been removed.

Also, what are the rules at your company regarding who is allowed to create DTS packages. DTS is becoming popular at my location with the developers and I've never been completely certain of the risks of allowing developers to create DTS packages.

Thanks, Dave

Shas3 Hall of Fame Points: 3671 More actions · Answer 7

I guess Steve is refereeing the Guest login ID in the msdb database. There is no way you can remove the public role. Public users access the DTS packages through the guest id, if you remove this that means you are blocking their access to the DTS packs. I did this on couple of server I manage at my client place. However on the other servers developers do need to have the access. They can do insert/delete/update through the DTS but not truncate or overwrite

Shas3