Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-10-10 : 13:33:35
|
| When I try to execute Exec Master..xp_cmdshell 'bcp "Select FileContent From FileUploadLog Where UploadedFileName = ''Summary062005.txt''" QueryOut "c:\Summary062005.txt" -c -Sdbserver -Uusername -Ppassword' I'm getting this error:EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.My Sys admin says he cannot give access to master db. This will be a part of a SP which I will call in an asp.net page.Any suggesstions?ThanksKarunakaran |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-10 : 13:39:51
|
| I am afraid your DBA is right...partially. Everyone has access to the master database, but giving access to xp_cmdshell is a bad idea from a security standpoint. Is there any way you can arrange to have the bcp command run outside SQL Server? By that I mean as part of an executable, or batch script? |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-10 : 13:53:12
|
| to execute xp_cmdshell you need to be a member of the sysadmin role. Your admin could also grant your login permission to execute xp_cmdshell, but this is usually considered a bad idea.you should have your DBA run this job from a sysadmin user, not your user. that way you do not have an issue.You might also explore creating a DTS package that accomplishes the same thing. You might have an easier time getting permissions granted for that, rather than the more general purpose (and potentially dangerous) xp_cmdshell.-ec |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-10-10 : 14:01:47
|
| I thought about DTS package...The problem is again calling from asp.net page... I dont want to make things more complex. All I need is to write some contents of column to a text file...other than bcp any thing else is there?Karunakaran |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-10 : 16:45:06
|
| Are FileSystem objects available to .asp code? Not being a programmer, I have no idea. Either that or can you direct the output to a file on the client via jscript, or some such thing? Maybe directly to an Excel file? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-10 : 18:28:32
|
| FileSystem objects are accessible from ASP code, as long as your network admin has not banished their use. But it seems to me that running exports of data from a web page is not exactly a smart thing to do. There are a lot of ways that could go horribly wrong.How about the idea of creating a recurring job that will query a control table and based on its contents, export what you need. Then you just need to populate the control table from your web page. So, the simplest would be that you populate the control table with the value for your WHERE clause, and at some appointed time, the Job runs it.Or perhaps you want to explore the FOR XML clause of the SELECT statement.---------------------------EmeraldCityDomains.com |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-10-10 : 18:36:00
|
quote:
Originally posted by AjarnMark
FileSystem objects are accessible from ASP code, as long as your network admin has not banished their use. But it seems to me that running exports of data from a web page is not exactly a smart thing to do. There are a lot of ways that could go horribly wrong.How about the idea of creating a recurring job that will query a control table and based on its contents, export what you need. Then you just need to populate the control table from your web page. So, the simplest would be that you populate the control table with the value for your WHERE clause, and at some appointed time, the Job runs it.Or perhaps you want to explore the FOR XML clause of the SELECT statement.---------------------------EmeraldCityDomains.com
that sounds like a really good solution. -ec |
|
|
|
|
|
|
|
|
Execute Permission denied on xp_cmdshell