| Author |
Topic |
|
JackOfAllTrades
Starting Member
12 Posts |
 Posted - 2009-04-14 : 15:45:51
|
| I've got this working. Dumps the content of the column to a txt file. Thinking about security and having xp_cmdshell enabled, what are my alternatives. I'm using this in a scheduled job that dumps the data to file so I can process that data via batch file from a different server. If there's a means to do this all in one batch file, then can I do that without enabling xp_cmdshell?EXEC master..xp_cmdshell 'bcp "select OneColumn from DBname.dbo.TestTable" QUERYOUT c:\test.txt -c -S SQLtest -T'Go-SteveColt's, Ruger's, Dan Wesson, & Kimber are my friends! Proud to be a U.S. Navy Veteran. |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-04-14 : 15:52:44
|
| Another viable option is CLR. You can create an assembly to perform this work for you. |
 |
|
|
JackOfAllTrades
Starting Member
12 Posts |
Posted - 2009-04-14 : 19:18:25
|
| CLR?-Steve |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-14 : 20:43:43
|
| Another option is to put the bcp command in a SQL Agent job as an Operating System task. |
|
|
|
JackOfAllTrades
Starting Member
12 Posts |
Posted - 2009-04-15 : 01:50:14
|
| that's actually my intent, to put this in an Agent job and schedule it. If I do that, can I turn off xp_cmdshell? -SteveColt's, Ruger's, Dan Wesson, & Kimber are my friends! Proud to be a U.S. Navy Veteran. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-15 : 06:52:22
|
| If you want to, sure. You can run it any time using sp_start_job. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-04-16 : 15:37:11
|
quote:
Originally posted by tkizer
CLR would be just as unsafe as xp_cmdshell though as in order to get to the file system, you have to grant elevated permissions on the database.
I think comparing an assembly with a permission set of EXTERNAL_ACCESS or UNSAFE to elevated permissions required for executing xp_cmdshell is a bit like comparing apples and oranges.Just to execute xp_cmdshell requires elevated permissions. Once you have permission to execute xp_cmdshell there is nothing preventing any command to be ran.The difference with CLR is the code to execute the file system needs to be written. Once written there are elevated permissions required to import assemblies that use EXTERNAL_ACCESS or UNSAFE permission sets (but not SAFE). However, once the assembly is created, all the permissions required to execute them do not require elevated permissions - just the typical permissions you would grant to execute objects (functions, procs, etc). And even if you can execute a CLR object, that does not imply you can execute anything through it... it will only do what the .NET code was written to do. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Alternative to .xp_cmdshell