SQL File List No Results via Web Page or Execute As

I have a stored procedure that gets data from SQL and also executes xp_dirtree to gather a file list, for linking. This works great when I execute the main stored procedure from SQL Mgmt Studio. However, when I try to execute it from the web page that dumps it into a .xlsx file, it does not give me the file list. (Whereas, the end users can choose the parameters they want for the results.)

1. Again, when I run via SQL it works fine.
2. The parameters being passed from the web page are correct.
3. I have given the domain account that runs SQL 'read' access to the directories and share. (Whereas, I have read this could be the issue)
4. I have tried to 'Execute as' when executing the xp_dirtree. (Doing this fails to give me a file list from within SQL Mgmt Studio)

Any advise would be helpful.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:

Presumably when running from SSMS you have logged on with sa permissions.

What rights (in SQL) does the account get when running from the web page?

Mark WillsTopic AdvisorCommented:
Agree with David...

It will most likely be how the web app connects to your environment and then how that in turn connects to SQL Server.

So, if you can run it locally, it probably means you have the correct mapping and permissions. But your web user doesn't.

Unfortunately, xp_DirTree is one of those "unofficial" extended stored procedures.

So, for a start it will be hiding in master and you might need access or explicitly tell your routine to use master.

use master

select sysusers.name, sysobjects.name, sysobjects.id 
from sysobjects, sysusers, sysprotects 
where sysobjects.id=sysprotects.id and sysprotects.uid = sysusers.uid 
and sysobjects.name =  'xp_dirtree' 

select * from syscomments where id = object_id('xp_dirtree')

Open in new window

and from the above, you will see that it uses a DLL to execute, so, that will be something else to check up.

Ideally you use supported techniques such as xp_cmdshell to get your list...
randhammackAuthor Commented:
I tried using xp_cmdshell it will not work once within the StProc.

It only seems to work using the xp_dirtree and when I set the domain account I am using for the website as a 'sysadmin', which I prefer not to do. Didn't even work just by granting execute permissions on xp_dirtree to the account.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Ummm... can usually get xp_cmdshell to work especially when protected inside a stored procedure.
randhammackAuthor Commented:
Just going to give the access to that login.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.